Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

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!

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Openform At Specific Record (again)
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

View Replies !   View Related
Open A Form And A Subform On A Specific Record
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.

View Replies !   View Related
Access VBA: OpenForm To A Specific Record
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.

View Replies !   View Related
How To Open A Form At Specific Record?

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

View Replies !   View Related
Open Specific Record - Syntax
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
End If

End Sub

I'm sure it's a syntax error on the "strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)" line.

View Replies !   View Related
Command Button To Open Specific Record
Hi All,

I have a form called "frmProducts" which is linking to table "tblproducts".

Easy Enough...

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

Any Ideas?

View Replies !   View Related
Using Data From Specific Record Of Subform
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!!
Bill Hesson
Tech Specialist II
El Cajon Valley High School

View Replies !   View Related
Open Form To New Record With Macro
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.


View Replies !   View Related
Subform Name Path - New Record Macro
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

View Replies !   View Related
Open Subform At Particular Record
Hi All

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 & "' "
DoCmd.OpenForm "frmProducts"
End If
End Sub

Below is my coding to open the form at a new record

Private Sub NewProduct_Click()
DoCmd.GoToRecord , , acNewRec
End Sub

Many thanks in advance for all assistance


View Replies !   View Related
Open Last Record In A Subform
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 ?


View Replies !   View Related
Scrolling A Subform To The Last Record On Open
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,

View Replies !   View Related
Openform Wizard Results In Blank Record
Hi All

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


View Replies !   View Related
Locking A Specific Field Of A Specific Record
Hello, I have just spent ages doing searches and reading everything I can on locking. But, I have yet to find an answer as to how I can lock a specific field in a specific record.

e.g. Staff enter customer details, then at the end of the day the admin (me) checks it over and presses a big old button that stops them from locking certain fields in the current record only - they must still have access to the unlocked fields of the current record, and it must not lock any other records.

I'm guessing there's some VB code in the form of fieldname.lock = true, but then it locks the field throughout the whole table!

Can anyone tell me how to do this please?

View Replies !   View Related
Open A Specific Form
I have a table that depending on a certain field in that table it will be able to open a specific form.

In other words I have multiple forms but only want a specific form opened when accessing a record if that record has a field with certain criteria

if that makes any sense


View Replies !   View Related
Open Specific Form
I have 2 forms one named "View" and the other "edit" on the form "view" the final user cannot edit any field as set from the form properties the form "edit" has no restrictions, what I need is to put a push button on the form "View" that will open the form "edit" showing the same record as on the form "View"
Hope I explained my problem :)

View Replies !   View Related

Copyright © 2005-08, All rights reserved