Form1 to Form2 Button in the same Record (OpenForm or GotoRecord??)

[Varvaroudis]

Hi!
I have two different Forms with the same Record Source table and I'm trying to set a button in the first form which opens the second form at the same record.

So far I tried the code below
Dim test as integer
test = 27
DoCmd.OpenForm "CustomerForm", , , "[CustomerID] =" & test

The test variable will be given a value from a textbox containing the CustomerID (which is the table's primary key)
The code above is opening the CustomerForm at the record with CustomerID=27, but it applies a filter and shows ONLY that record. Is there a way to just go to that record?

I've also experimented with docmd.gotorecord to no avail.

 

[stopher]

Hi and welcome to the forum.

I suggest that in the On Load event for your second form you use the FindRecord method e.g.

DoCmd.FindRecord 27, acAnywhere, , , True, acAll, False

You will want to take a look at the details of this method for your own purposes e.g. you may want to set focus to a specific field then only run the search on that field.

You will of course want to get the 27 from your first form. If this is in a text field that just reference the field on the first form from the second. If you want to pass a variable to the second form then use openargs.

hth
Chris

 

[Varvaroudis]

Thanks!!!! Worked like a charm! You were right about the Setfocus stuff!
Here's the code I used:

Dim CustomerNumber As Integer
CustomerNumber = txbClientKey.Value
DoCmd.OpenForm "Form2", acNormal
Form_Form2.txbClientKey.SetFocus
DoCmd.FindRecord CustomerNumber, acEntire, True, acSearchAll, , acCurrent, True

(Both forms have a txbClientKey textbox with the customerID)
The entire code is in the Command Button in form 1. Setting focus to an element in form2 enables the FindRecord command in Form2. No need for code in form2 at Openform event!

Now I have a small navigation panel in my form-footers!

Thank you!

 

[Toolguy]

Doesn't the wizard do the same thing when you place a button on the form?

 

[Varvaroudis]

Nope, the wizard has only 6 options for record navigation (Goto first, last, next, previous record, find (which call the Find window, as Ctrl+F) and find next).
With the code above, you can navigate to a specific record (p.e. the one where "James Brown" is in the name textbox, or "67" is in the CostumerID textbox etc)

 

[dark11984]

Hi just tried this but got an error message saying "overflow"... does anyone know what that means?

Cheers

 

[dark11984]

Hi I got this to work, just had to change
dim customer number as string rather then integer.