opening form based on a differant tables field

[bloody_football]

I have a form which is a query, it is a quote and when it is finished the client information goes to the 'client' table (for re-use) and the financial data goes into the 'quotes' table. No when the user completes the form the form closes and another form opens with the same information.

My problem is this - each new quote gets an ID number which is saved into the 'quotes' table but when the form is automatically opened it says it can't find ID. yet when I open the second form manually it is fine.

DoCmd.OpenForm "FORM - Find Quote or Agreement", , , "[ID]=" & Me![ID]

But the ID number is not in the form (Me![ID] part), it is now in the 'quotes' table. What is the correct code for this?

 

[WayneRyan]

bloody,

The truth is, when you close the form, the ID that was just added
is lost forever.

You can keep the form open and use its ID, or save it to a Global
variable, or use a DMax (Or some) function call to retrieve it from
the table.

Basically, when the form saves it to a table, if some other entity
within the app wants to use it, it must "know" WHICH value to get
from the table.

Wayne

 

[bloody_football]

ID has been created in the table 'quotes', the question is how do I now open the next form with that ID number?

 

[PierreR]

Your code was right. Something else must be wrong. The wizard uses Dim declarations. Here's a solution you can look at.

 

[bloody_football]

First stupid question - What does 'Me' actually stand for? (for example Me![ID])

Next question - I still cannot get the program to look for the record based on the ID from another table.

I know that

DoCmd.OpenForm "FORM - Find Quote or Agreement", , , "[ID]=" & Me![ID]

will open a Form based on the ID from the Form I am working on, but I need to open the Form based on the ID number from the table 'quotes'.

 

[WayneRyan]

Bloody,

If you're looking for the LAST record entered, you can probably use
the DMax function (based on the Autonumber field).

Wayne

 

[Ukraine82]

If you wish to open a different form with same record. Paste this code in a command button.

On Error GoTo Err_Handler

Dim strYourPrimaryKeyFieldName As String
Dim strForm As String

'Form you wish to open with same record
strForm = "FormName"
strYourPrimaryKeyFieldName = "[YourPrimaryKeyFieldName]='" & Me.FieldName & "'"

DoCmd.OpenForm strForm, , , strYourPrimaryKeyFieldName
Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description & " " & Err.Number
Resume Exit_Here
End Sub

hth,

Michael

 

[bloody_football]

As Wayne pointed out - the table "quotes" will have many IDs. WHICH one do you want?

As well as learning access I still need to learn how to ask questions. The field from the table 'quotes' is actually called 'ID' The field 'ID' is the autonumbering primary key in that table.