Opening a second form to the Same Record Number

[kmeulemans]

All,
I am in the process of creating a maintenance database.
I have created a form "A" has an area for entering the work required.
I have a second form "B" that is linked to form "A" with a button for entering the work completed.
How do i get the second form to open to the same Record number as the first form?
Example:
So if i am on record 36 in form "A", I hit the button to open form "B" it also opens to record number 36.
Any help would be appreciated.

 

[boblarson]

1. try not to think record "number." Think record ID.

2. You will need to issue a save command on the first form just before opening the second if you don't want a conflict to occur, if you are on the same record in the same table.

3. To open to the same record you would use something like:

' first save the record IF there are changes
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm "YourFormnameHere", acNormal, , "[RecordIDFieldName]=" & Me!RecordFieldName

Oh and ...

 

[kmeulemans]

Thanks for the Welcome! I'm sure i will be using the forums frequently as i have 2 databases already to build and probably more in the works.
Would that code be any different since the data in form B is going to a different table?

 

[boblarson]

Thanks for the Welcome! I'm sure i will be using the forums frequently as i have 2 databases already to build and probably more in the works.
Would that code be any different since the data in form B is going to a different table?

Not really, But the "[RecordIDFieldHere] = " is the field you have on the Form B and the Me!RecordIDFieldHere is the field you have on Form A.

 

[kmeulemans]

One More dumb question...where in the button code would i paste that code you provided?

 

[boblarson]

One More dumb question...where in the button code would i paste that code you provided?

The click event. If you're not sure how to get to the VBA window, look at this. The event shown is the CURRENT event, but you can just substitute CLICK instead.

 

[kmeulemans]

I am missing something.
This is what i put in on the Click event.
Its saying there is a bug..


Private Sub Close_Work_Order_Click()

' first save the record IF there are changes
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm "frmClosed", acNormal, , "[RecordIDFieldName]=" & Me!RecordFieldName

The debugger says it is the last line of code...

 

[boblarson]

Is RecordIDFieldName the actual name of the field in your table B?

Is RecordFieldName the actual name of the field in your table A?

 

[missinglinq]

Bob's code was based on RecordIDFieldName being a numeric datatype. If it is, in fact, text, the syntax would be:

DoCmd.OpenForm "frmClosed", acNormal, , "[RecordIDFieldName]= '" & Me!RecordFieldName & "'"

 

[boblarson]

Linq has a good point, so you need to know what datatype you are using. But, my main question is - are those really the field names? It looks more like the sample names and not something that would really be in use. So you have to use your REAL field names instead of the made up ones.

 

[kmeulemans]

No, those are not the "real" field names.
My question is, how are the fields even relevent?
I have 3 different fields in Form "B" and Several in form "A"
See the screen Shot.

 

[boblarson]

No, those are not the "real" field names.

You have to use YOUR field names.

My question is, how are the fields even relevent?

The FIELDS are what contain the data, so the code I showed will return a form filtered to that specific record.

See the screen Shot.

I can't see the image as external sources like imageshack are blocked by my work. Just upload the screenshot here if you can.

 

[krellda]

I need to continue this thread, i'm trying to do a very similar thing, i have two forms one for clients and one for car hire rates, when i open a page for a client i want to make a button to open the relavent hire rate page.

I see i need to use primary keys, what would be the best thing to use as the key?

Currently i'm still testing everything, what functions i can use so this is very very incomplete, and mostly temporary but

Clients
----------------------
First Name
Surname
Address
Clients vehicle
Hire Vehicle

Vehicle and hire rates
----------------------
Hire Vehicle
Date Out
Date In
Rate of Hire

Hire vehicle is a lookup from another table, that contains the names, reg plates and hire rates of all the vehicles, on both of these two forms.

Hope you can help
Thanks

Dan

 

[krellda]

Done some thinking, i would need to make a button to open the hire rate page on a new record aswell for when i have new clients. Also i think the best thing to do would be to use the reg plates as a primary key, but to not add them into any form other than the Owned vehicles form. If you know of a good way to go about doing all of this please help.

Thanks

Dan

 

[kmeulemans]

Sorry it took me so long to post another reply.
I have tired to upload the screen shot again....Maybe it will work.
The little buttons toggle back forth between the 2 forms.
I need the form "B" to open to the same "Number"

 

[krellda]

This is pretty much exactly what i need too, if anyone can help I, and i'm sure kmeulemans will really appreciate it.

Thanks
Dan

 

[krellda]

I need to post to move this back up the forum.

Would it be a case of making primary keys and linking them, and then making some kind of function, or macro or whatever to open the other form to the form of the same primary key? if so what function can do this?

 

[krellda]

I have figured out the solution to this problem.
I feel i should post it here to help the other person involved.

The simplest way to do this is to create a Field with unique information
in my system it is 'Case Number' i fill in the case number which currently is just 1, 2, 3 and so on but when finalise everything i may use something more meaningful.
Make this field on both tables and using the relationships page, link them together (not really sure if thats necessary as i am writing this at the time of solving the problem so i'm just saying what i did)
Then make a button on the form you want to open another form from and select form operations>open form>[name of form you want to open]
and select open the form and find specific data to display.

Then select the two fields which are identical (in my system 'case number') and the rest is just nameing the button etc.

If all went smoothly you should be able to click this button and bring up the corresponding form to the one you have open. its working on mine and i didn't have to use any complicated VB or SQL

Thanks anyway, this forum has been very helpful in other areas i have needed help with