Using OpenForm Macro To Open A Subform To A Specific Record
Hello, fantastic site you have here!
I have a form based on a table called TermWithPaysStoredData that shows a single row for everyone in the table with a small amount of info from the table and has controls for a user to enter dates which writes to that same table.
I have a subform that reads that same table but has much more information about each row. I would like to add a button to each row of the main form that says "More Info". When clicked, it should open the subform to the same employee.
The field I want to match is called ID. My main form is called fTermWithPays and my subform is called fTermWithPaysMoreInformation.
I tried using the OpenForm method but that either opens the form to the first record or filters and shows only one blank record.
I'd like to use a macro for this if possible because I don't know VB.
Any ideas? Let me know if more explanation is needed.
Thanks very much!
Hi All, I have a command button with a macro that opens another form. I want to open record 0001 in the 'openning' form if the record on the original form is 0001 or 0002 if 0002 and so on. I hope this make sense. I have used this in the Where ..[GroupID]=[Forms]![Group Details]![BookingID] but I think this is either wrong or completely unrelated to what I want to do because all it does is put in filter that don't want.
Sorry about this. I know there is loads on here about this but I just can't get it to work.
I have a basic table of dates.
One form based on the dates via a query
When the form opens, I want it to show today's date but allowing access to all the other date records (so no filter).
Any help please
I am trying to open a single form with a single subform with a command button. I would like to open the form with the subform on a specific record but I am not entirely sure how to do this, I was wondering if someone had any advice that could help.
I am not a specialist, but am trying to create an application with Access 2000to manage our company’s orders. I have a table called ‘Service’ containing descriptive data on the type of service requested. This table is linked to several other tables, with ‘ServiceNo’ as the primary key linking most of them. The service number is not an autonumber, but a counter incremented through code, for specific reasons that I won’t develop here.
I have created two forms: one to consult all the service records (F_Service_Consult), with a button to open another form that allows the user to add a new service record (F_Service_Add).
When the new service form opens, the service number counter is incremented and this number is assigned as the new 'ServiceNo' in a new record added to the ‘Service’ table (using the SQL ‘Insert’ command). I would like this form (F_Service_Add) to open on this record only. In the code that creates ‘ServiceNo’, the correct value is contained in a variable called ‘lngServiceCount’. I have tried using this code to open my form:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "F_Service_Add"
stLinkCriteria = "Forms![F_Service_Add]![ServiceNo]='" & lngServiceCount & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
When I run the code step-by-step, the correct value is shown for ‘lngServiceCount’ when the mouse hovers over the variable (for ex. ‘155’). When the form opens, I have 1 record (Filtered), but nothing shows in the ‘ServiceNo’ text box.
I have deactivated the code that is in the ‘OnCurrent’ event procedure of this form to avoid any interference.
There are many sub-forms on the main form, which also contain the ‘ServiceNo’ in text boxes.
When I use this command to open the form:
DoCmd.GoToRecord , , acLast
the form opens correctly, with the correct ‘ServiceNo’, but ALL the records are opened.
The variable type for ‘lngServiceCount’ is a long integer. (I hope that is the correct term in English, because I am using a French version of Access).
I would be very grateful if anyone could tell me what I’m doing wrong. I’ve tried to be clear, but if more information is required, I would of course provide what’s missing.
Thanks in advance.
I have created a macro with the purpose of opening a specific form (frmCustomerJob), which uses a table (tblClient) as its data source, and jump to the record specified in a combo box on a different form which is open.
I have used the OpenForm action, together with the Where Condition [ClientID]=[Forms]![fdlgExistingCustomer].[ExistingCustomerDropdown]
ExistingCustomerDropdown is the name given to the combo box an dfdlgExistingCustomer is the form upon which the combo box is placed.
My problem is that upon running the macro I am prompted to enter a value as opposed to the value being taken from the combo box as is intended. Upon typing a value, however, the form is displayed at the correct record.
The issue is obviously related to the macro not picking up on the combo box, yet I cannot them to work together and use the specified value. Can anybody please help?
Is it possible to create a macro that would open windows explorer and open up a specific folder?
I have a facility that exports files to a certain location and would like to add a button to the form that opens this folder so the user can access the files?
I am trying to create a macro that will open an import window to a specifc folder. This will prevent having to search the for the folder everytime my import button is pressed.
Good Afternoon, I am trying to run a macro and one of the commands I need is to open a specific excel spreadsheet after the Macro has run. I can figure out how to open Excel, but not a certain file.
I have tried RunApp but that didn't work...
Edited by: imjustagamer on Tue Nov 9 13:12:31 EST 2004.
I want my subform to open on a specific location on the screen and only be a specific Size. Any idea how to do that?
I want to open a new form which is similar to the form it will be opened from. Only difference is the new form I want to open is designed for printing purposes.
I'd like to access the this new form by hitting a button that opens the print form for a user given order number. Any ideas if this is possible? Thanks
I am trying to use the double click event of a listbox to open a form to a specific record. The problem I have is when I double click a record, Access pops up a box asking for PONumber (which is in column 0 of the listbox).
Here is the code that I am using:
Private Sub lstPOInfo_DblClick(Cancel As Integer)
Dim boStatus As Boolean
Dim strDocName As String
Dim strLinkCriteria As String
strDocName = "Orders"
' function checks to see if the form is open or not
boStatus = fIsLoaded(strDocName)
If boStatus = True Then
strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)
Forms![Orders].FilterOn = True
Forms![Orders].Filter = strLinkCriteria
strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)
DoCmd.OpenForm strDocName, , , strLinkCriteria
I'm sure it's a syntax error on the "strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)" line.
I have a form called "frmProducts" which is linking to table "tblproducts".
The form shows all the Products information (fields).
I have a "More Info" command button (cmdMoreInfo) next to every record in the form.
When the command button is clicked I would like it to open up another form "frmMoreInfo".
But On frmMoreInfo I would only like it to show that specific product.
I have tried the following code on the on click event on the command button but it was always asking me for the value of Form![frmMoreInfo]![txtInternalCode]
Code:Private Sub Command12_Click()On Error GoTo Err_Command12_ClickDim stDocName As StringDim stLinkCriteria As StringstDocName = "frmMoreInfo" stLinkCriteria = "Form![frmMoreInfo]![txtInternalCode]=" & Me![txtInternalCode]DoCmd.OpenForm stDocName, , , stLinkCriteriaExit_Command12_Click:Exit SubErr_Command12_Click:MsgBox Err.DescriptionResume Exit_Command12_Click End Sub
I program in access but am new to VB. I am trying to open Form B to the record that is selected in a listbox on the current form (Form A).
What is the equivalent of:
DoCmd.OpenForm stDocName, , , strCond
in visual basic.
I know how to open the form using stDocName.show, however I am unclear on how to open the form to a specific record.
I am using ADO recordset
I am trying to get data from a specific record in a sub form. and save a result in the same record.
I am using the following fields
"teacher type","Class", "Step", "Hours", "Pay"
I have two teacher types, Contract and hourly. a teacher can be either of these or both. I am taking the class and step info from the subform, looking up the data in a table to determine their pay and saving it to the "pay" field.
this works fine as long as I only have one record. if I add the second record the calculation changes bot of the records "Pay" field value to the same thing. what I want to do is have it only calculate the pay for the contract teachers and have it saved to the proper record and the same for the hourly teachers.
I have seperate routines for the calculation of each type.
Any help will be appreciated!!
Tech Specialist II
El Cajon Valley High School
I have a DAP that has a list of records in tabular format. One of the options I want to add is a button that opens a different page that allows modification of the record. example:
I have the record...in the row is a button that says "Close Issue" (its an issues db). I want this button to open another DAP and load the specific information from that record onto the DAP (ie Title, Date entered, etc), but I also want to be able to make changes to fields in that record (ie Report Link for adding a hyperlink to a report). How can I make this button open a new page and load a specific record?
Does anyone know how to open a form from a button and have it go to a specific record based on an ID but not filter the newly opened report.
Here's my code:
DoCmd.OpenForm ("frmTradeshow1"), , , "TradeshowID=" & TradeshowID
But, it Filters frmTradeshow1. I just want it to go to the correct TradeshowID.
I have a simple code that is supposed to open a form to a specific record. However, the code fails returning a "Update or CancelUpdate without AddNew or Edit". I do not have an update or cancelupdate in my code. Is it possible there is code on my form that is running OnCurrent that might conflict?
Here is my code:
Private Sub Altair_Review_ID_Click()
On Error GoTo Err_Index_Click
Dim varID As Variant
varID = Forms!Index.Altair_Review_ID
DoCmd.OpenForm "MAIN", acNormal, , , acFormEdit, acNormal
If IsNumeric(varID) Then
.FindFirst "[Altair Review ID] = " & varID
If Not .NoMatch Then
Forms!MAIN.Bookmark = .Bookmark
DoCmd.Close acForm, "INDEX", acSaveYes
Does anyone have any suggestions of what I am doing wrong? I think I had this code working at some point, but I must have made a change somewhere that is causing it to fail.
Thank you very much for your anticipated suggestions!
I have the following function for my database.
Basically someone completes a task in a form in access, when they click finish an e-mail is sent to them with an ID in for the what they have been doing
When they get that e-mail I would like for them to be able to click on a url/or file location address which will take them back into access to the specific record that they have edited
How would I do this?
basically I want to open a specific record in access from an e-mail
is this possible?
I'm in the final stages of finishing a project. To add some ease of use I'd like to find a way to go from my customer orders form directly to the same customer record in my customer master file. How can I do this?
I've tried using "onclick" but I'm missing something...
Form:CustOrder - The customer information in this form is protected. I'd like to be able to click/double click the customer number control and go directly to that customer in the customer master form.
I have a new dillema..
I have a form with a subform on it. This subform lists a number of records from a table one after another in a Datasheet view. I want to be able to select a record from that sheet and open a form that displays the details for that record. Is this possible? Can I do it with a double click on the record or do I have a seperate "OPEN" button on the side?
Any help would be appreciated.
Thanks for all the help..
I have a Form with a sub form in Data sheet view and i want to be able to click on a record and have it open up the record in another form so it can be edited. To open it i was thinking maybe you could double click on the record or some how select it then push a button. If anyone can help it would be appreciated.
Basically I'm new to Access and having problems opening up a form to a specific record based on input from the user. In this code, Combo Box 29 has the form names, and Combo Box 31 has the record names. I want to open up whatever form the user selects in Combo Box 29 to the record they select in Combo Box 31.
Thanks for your help!
Private Sub Combo31_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.OpenForm Me.Combo29, , , stLinkCriteria
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Bulb Type] = " & Me![Combo31]
Me.Bookmark = Me.RecordsetClone.Bookmark
Hello. I have a form with a subform imbedded in it. My form displays the Office info and the subform displays the Contacts associated with that office. The design is such that there is only room to display one Contact at a time without scrolling. I may change this, but I am hesitant at the moment.
Because a user might only know the contact and not the office, I also have a Form/Subform combo in the reverse. Where the Form displays one Contact and the Subform displays all the offices associated with that contact. On the Office subform, I have a button called "Go!" next to each Office entry. So when you click it, it automatically opens and displays the corresponding office on my first Form, with all the contacts displayed underneath. It links by Office_ID = Office_ID.
However, I can't get it to also link Contact_ID to Contact_ID. Which means the right Office will pop up, but then you have to scroll down the subform to get to the Contact you were searching for to begin with. Which is annoying when an Office has a bunch of contacts.
I tried messing around in the code the button wizard generated but no such luck. Does anyone know a way to do this?
Let me know if you need more info. The whole thing is kind of hard to explain.
I really hate being stupid about this, but I'm running around in a circle and it's making me dizzy. All I want is a simple macro that opens a form (with a subform) at a new record from a button. I know I am missing something obvious so I will let others see if they can find out what has gone wrong. The current macro is:
GoToRecord .. ObjectType(Form) .. ObjectName(FormName) .. Record(New)
On the property sheet of the form the following are all set to yes:
Allow Filters, Allow Edits, Allow Deletions, Allow Additions, Data Entry (I suspect the problem is here)
Record Locks is set to No Locks.
When I open the form from the button, it looks right, and I can enter the transaction information, but when I try to enter the subform information or save it , I get "The Microsoft Jet database engine cannot find a record in the table 'NewContacts_tbl' with key matching field(s) 'ContactID'". I get the same message with Allow Data Entry set to no.
And yes the key field IS there, in spite of what the Microsoft Jet database says.
I hope everyone gets a chance to enjoy the holidays and thanks to all of you for your past help and my current (relative) sanity.
I've got a form with a subform on it that has a subform on it (nested subforms - Customers form with computers subform, which has a software licenses subform on it)
I'm trying to put a button on the top-level customers form that will jump to a new record in the software license subform-subform (the licenses form is displayed as continuous with a blank at the end for new license entry). I want to set the OnClick action to move the record selector on the software licenses subform to the last (new) record in the continuous form display. I can't figure out the appropriate path to use as the Form Name in my GotoRecord macro.
Is there a special trick to use when referencing subform controls?
slaughter at mizzou dot edu
I am looking to open a form that contains a subform to a specific record. If I was to open this form independently, the code I would use would be:
Code: DoCmd.OpenForm "Takeoffmasteraudit", , , "[CostCodeID]=forms!takeoff!tbcostcode", , , "Takeoff"
So, on my master audit form, the takeoffmasteraudit is the child. I am wondering if there is some syntax to run the where condition on a subform?
Thanks for all the help guys.
I have a subform called frmProducts and on it I have 2 command buttons One is to use the current open form to jump to a particular product to make it easier to edit the details and the other is to use the current form and jump to a new record.
Now I have this working when running the form on its own but when I try and run it as a subform it does not work. I am not sure how to adjust the coding to let this happen.
the Main forms name is "MainForm"
Below is my current coding to open the form at a particular record
Private Sub EditProduct_Click()
Dim Msg As String
Dim Title As String
Dim Defvalue As String
Dim Answer As String
Msg = "Enter a Product Name: "
Title = "Open Prouct Form"
Defvalue = ""
Answer = InputBox(Msg, Title, Defvalue)
If Answer <> "" Then
DoCmd.OpenForm "frmProducts", , , "[ProductName]='" & Answer & "' "
Below is my coding to open the form at a new record
Private Sub NewProduct_Click()
DoCmd.GoToRecord , , acNewRec
Many thanks in advance for all assistance
I have a form, with a subform in it.
I am trying to add a record through the subform then once added I want to display that record..
at the moment I add the record but the first record is shown.
I have tried adding :
DoCmd.GoToRecord , Forms!frmviewscheme![QScheme_Establishment subform], acLast
but recieve a run-tim error 2498.
can anybody help me ?
Is there a way to automatically scroll a subform on opening so the subform is filled with the last few records along with the new record data entry line?
Thanks for any help,
Hi! I'm using a customer form with a subform showing all the orders that belongs to that customer.
When i click on the order number in the subform, I want to open the "Order"-form and show that specific order. But I only get to the first record...I'm trying with this code(This code worked fine in another db, but from a form, not a subform):
Dim specificOrder As String
Dim rs As Object
specificOrder = OrderNumber
Set rs = Forms![Orders].RecordsetClone
rs.FindFirst "[OrderNumber] = '" & specificOrder & "'"
If Not rs.EOF Then Forms![Orders].Bookmark = rs.Bookmark
What is wrong??
Can anyone help by advising what code to use to ;
Open Acrobat Reader and display a specific .pdf file.
Some products in my database have associated .pdf drawings. It would be nice to have an option to display these.
The .pdf file name is held in a 'Text Box' on a 'Products' form.
The folder location on our server is known.
I have prepared a 'Command Button' on the form. The idea was to use the 'On Click' property to open Acrobat and display the file.
Appreciate any advice
I'm currently working on a product catalogue, i have a product data screen (frmProductLookup) one of the field s i have is "Replacement" for when a product is superseeded. What i want to achieve is to be able to click on a button and open a second product data screen that automatically goes to the record for the replacement product.
I've currently tried creating a duplicate data form (frmProductLookup2) and used the openform wizard, but for some reason this pulls up a blank record...any ideas