Link to home
Create AccountLog in
Avatar of petePrinter
petePrinterFlag for United Kingdom of Great Britain and Northern Ireland

asked on

open form and load values

i have frmenquiry that loads from a button click on another form.
it uses dmax to take the next number from a table.
User adds data in some fields and then closes the form which saves the data.
all ok.
The user would like the option to close the form and open another (Dmax adds 1) that contains two Pieces of data that is common from the first form.
i have set two variables PNumber and PDate to hold this in the event.

How do i load the new form so that it can access these variables and load the form to avoid retyping please?
Thanks for looking at this.

Private Sub cmdSave_NewCopy_Click()
Dim PNumber As String
Dim PDate As Date
Dim MysSQL As String
MySQL = "INSERT INTO tblEnquires([CustAutoNum],[contactID],[enqNumber],[enqCustRef],[enqDate],[enqDetail])SELECT" & Chr(34) & Me.txtCustAutoNum & Chr(34) & "," & Chr(34) & Me.txtcontactID & Chr(34) & "," & Chr(34) & Me.txtenqNumber & Chr(34) & "," & Chr(34) & Me.txtenqCustRef & Chr(34) & "," & Chr(34) & Me.txtenqDate & Chr(34) & "," & Chr(34) & Me.txtenqDetail & Chr(34) & ";"
DoCmd.SetWarnings (False)
DoCmd.SetWarnings (True)
PNumber = Me.txtenqCustRef
PDate = Me.txtenqDate
'MsgBox PNumber & " " & PDate
DoCmd.Close acForm, Me.Name, acSaveYes
DoCmd.OpenForm "frmEnquiry"
End Sub

Open in new window

Avatar of als315
Flag of Russian Federation image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Generally, you should try to use as few global variables as possible.

In your case, you can avoid this by closing your first form AFTER opening the second.

DoCmd.OpenForm "frmEnquiry"
DoCmd.Close acForm, Me.Name, acSaveYes

If you do this, you can refer to the fields on your first form in the load or open event of your second form like this:

Something = Forms!YourFirstFormsName.txtenqCustRef
SomethingElse = Forms!YourFirstFormsName.txtenqDate
Another method is to close Form1 From Form2 after getting the needed data:

Something = Forms!YourFirstFormsName.txtenqCustRef
SomethingElse = Forms!YourFirstFormsName.txtenqDate
DoCmd.Close acForm, Forms!YourFirstFormsName

The acSaveYes doesn't seem needed here.  It saves design changes.  Data in Access forms automatically gets saved.
Avatar of petePrinter


looking at this

thank you
because a use the dmax function to get the next number this is not saved until the form closes.
the new form would then have the same number

can this be done with arguments?
Thank you this works fine i created another form so that it will load these variables when called.

Do i have to release these global variables please?