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

Hi
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.
Pete

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.RunSQL MySQL
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

ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
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

ASKER

als315
looking at this

mbizup
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?
als315
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?