rltomalin
asked on
Passing data from one form to create a record in another form
Hello experts,
I have a form – frmJobDetails - that displays a record.
Relevant fields in this record are:
JobID
ClientID
I also have another function with a form to create a ClientLog record. On this form the user enters the ClientID and the JobID together with some other information.
This all works fine.
But I would like to add a button on the frmJobDetails form that goes straight and opens the form to create a new ClientLog, carrying across the JobID and ClientID onto the form and also set some of the fields in the form to defined values.
I cannot figure out the code that needs to go in the button’s click event. Can someone give me a start please?
Best regards
Richard
I have a form – frmJobDetails - that displays a record.
Relevant fields in this record are:
JobID
ClientID
I also have another function with a form to create a ClientLog record. On this form the user enters the ClientID and the JobID together with some other information.
This all works fine.
But I would like to add a button on the frmJobDetails form that goes straight and opens the form to create a new ClientLog, carrying across the JobID and ClientID onto the form and also set some of the fields in the form to defined values.
I cannot figure out the code that needs to go in the button’s click event. Can someone give me a start please?
Best regards
Richard
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the feedback guys. Sorry about the delay in responding but somethings come up and I had to move away from the interesting stuff.
I don't have time to try this right now (not until next week actually), but I think I get the idea.
To answer the question, we are using 2003.
Just to clarify, would I use something like:
docmd.OpenForm "frmNewClientLog",,,,acFor mAdd
Forms!frmNewClientLog!LogT ype= "Initial Evaluation" << setting the value to a constant
Forms!frmNewClientLog!JobI D= #JobID# << setting the value to a value from the main form
Forms!frmNewClientLog!Clie ntID= #ClientID#
Regards
Richard
Picking up the field values from the current form by putting them inside # chars?
I don't have time to try this right now (not until next week actually), but I think I get the idea.
To answer the question, we are using 2003.
Just to clarify, would I use something like:
docmd.OpenForm "frmNewClientLog",,,,acFor
Forms!frmNewClientLog!LogT
Forms!frmNewClientLog!JobI
Forms!frmNewClientLog!Clie
Regards
Richard
Picking up the field values from the current form by putting them inside # chars?
NO.
Only date VALUES go inside # # (as in #2013-12-30#)
numbers have no quotes nor #
Text has " "
Only date VALUES go inside # # (as in #2013-12-30#)
numbers have no quotes nor #
Text has " "
ASKER
OK that's fine.
So how do I format a statement that says:
JobId on frmNewClientLog (the new record form) = JobId on frmEditJob (the form that has the button on it)?
I think that mbizup suggested a possible direction, but only for 2007.
Regards
Richard
So how do I format a statement that says:
JobId on frmNewClientLog (the new record form) = JobId on frmEditJob (the form that has the button on it)?
I think that mbizup suggested a possible direction, but only for 2007.
Regards
Richard
<<But I would like to add a button on the frmJobDetails form that goes straight and opens the form to create a new ClientLog, carrying across the JobID and ClientID onto the form and also set some of the fields in the form to defined values.>>
Do that in Access with the openargs arguement. It's a single value, but you can pass a delimited string to seperate multiple values. Here's an example:
DoCmd.OpenForm "frmCustomerAddOnly", acNormal, , , acFormEdit, acDialog, "ADD;SETCTRLTODATA=txtCust omerID:" & NewData & ";EXITTOFORM=frmLoad"
Using the semi-colon (;) as a segment delimiter and a colon (:) as a element delimiter.
The segment starts with a tag (i.e. SETCTRLTODATA) and then if more then that, has an equal sign then followed by elements. In the case of SETCTRLTODATA, the elements are pairs; control to set the data to, followed by the data.
I then call this routine in the called forms OnOpen:
' Set any controls required by calling form.
varOpenArgs = Nz(frm.OpenArgs, "")
If Not (varOpenArgs = "") Then
var = glrGetTagFromString(varOpe nArgs, "SETCTRLTODATA")
If Not IsNull(var) Then Call SetControlsToData(frm, Mid$(var, 1))
End If
and the SetCtrlToData() procedure then looked like this:
Sub SetControlsToData(frm As Form, strData As String)
' Set controls on frm to values contained in strData.
' Delimiter is a ":". Format of strData is control name: value.
Dim intPairNumber As Integer
Dim varControlName As Variant
Dim varData As Variant
intPairNumber = 1
Do
varControlName = dhExtractString(strData, intPairNumber, ":")
If varControlName = "" Then Exit Do
varData = dhExtractString(strData, intPairNumber + 1, ":")
frm(varControlName) = varData
intPairNumber = intPairNumber + 2
Loop
End Sub
glrGetTagFromString() and dhExtractString() are procedures from the Access Developers Handbook published by Sybex, so I can't reproduce them here but in general, they simply split a string based on a delimited, which you can do with Split().
The above example is a popup form that allows a new customer to be added after someone triggered a NotInList event and populates the customer name on the popup with what they typed in the combo originally.
Jim.
Do that in Access with the openargs arguement. It's a single value, but you can pass a delimited string to seperate multiple values. Here's an example:
DoCmd.OpenForm "frmCustomerAddOnly", acNormal, , , acFormEdit, acDialog, "ADD;SETCTRLTODATA=txtCust
Using the semi-colon (;) as a segment delimiter and a colon (:) as a element delimiter.
The segment starts with a tag (i.e. SETCTRLTODATA) and then if more then that, has an equal sign then followed by elements. In the case of SETCTRLTODATA, the elements are pairs; control to set the data to, followed by the data.
I then call this routine in the called forms OnOpen:
' Set any controls required by calling form.
varOpenArgs = Nz(frm.OpenArgs, "")
If Not (varOpenArgs = "") Then
var = glrGetTagFromString(varOpe
If Not IsNull(var) Then Call SetControlsToData(frm, Mid$(var, 1))
End If
and the SetCtrlToData() procedure then looked like this:
Sub SetControlsToData(frm As Form, strData As String)
' Set controls on frm to values contained in strData.
' Delimiter is a ":". Format of strData is control name: value.
Dim intPairNumber As Integer
Dim varControlName As Variant
Dim varData As Variant
intPairNumber = 1
Do
varControlName = dhExtractString(strData, intPairNumber, ":")
If varControlName = "" Then Exit Do
varData = dhExtractString(strData, intPairNumber + 1, ":")
frm(varControlName) = varData
intPairNumber = intPairNumber + 2
Loop
End Sub
glrGetTagFromString() and dhExtractString() are procedures from the Access Developers Handbook published by Sybex, so I can't reproduce them here but in general, they simply split a string based on a delimited, which you can do with Split().
The above example is a popup form that allows a new customer to be added after someone triggered a NotInList event and populates the customer name on the popup with what they typed in the combo originally.
Jim.
<<
I think that mbizup suggested a possible direction, but only for 2007.
>>
Yup - I was just tossing that out there since the question didn't state what version you were using.
That's one of the really GOOD new features in Access 2007 and higher and I think a lot of us (myself included) tend to forget it is there, but it won't work for you (Access 2003).
I think that mbizup suggested a possible direction, but only for 2007.
>>
Yup - I was just tossing that out there since the question didn't state what version you were using.
That's one of the really GOOD new features in Access 2007 and higher and I think a lot of us (myself included) tend to forget it is there, but it won't work for you (Access 2003).
ASKER
I'm getting confused now I'm afraid!!
Is there no simple VB commands that can be used when opening a form to pass field values across - sort of like:
docmd.OpenForm "frmNewClientLog",,,,acFor mAdd
Forms!frmNewClientLog!LogT ype= "Initial Evaluation" << setting the value to a constant
Forms!frmNewClientLog!JobI D = Me.JobID
Forms!frmNewClientLog!Clie ntID = Me.ClientID
Regards
Richard
Is there no simple VB commands that can be used when opening a form to pass field values across - sort of like:
docmd.OpenForm "frmNewClientLog",,,,acFor
Forms!frmNewClientLog!LogT
Forms!frmNewClientLog!JobI
Forms!frmNewClientLog!Clie
Regards
Richard
The code you have just posted should work, provided all the names and data types are correct
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys
Unfortunately I am away from work now until Monday.
So will leave this for now and I will continue next week.
Regards
Unfortunately I am away from work now until Monday.
So will leave this for now and I will continue next week.
Regards
<<Is there no simple VB commands that can be used when opening a form to pass field values across - sort of like:>>
Sure. One form can refer to any other open form.
Generally you only pass data when the calling form may be unknown. In the example I gave, adding a customer may occur in several different places in an application. If you make a direct reference to a calling form, then your stuck making multiple called forms if you need to use it more then once.
If you pass the data, you only need one copy of it.
Jim.
Sure. One form can refer to any other open form.
Generally you only pass data when the calling form may be unknown. In the example I gave, adding a customer may occur in several different places in an application. If you make a direct reference to a calling form, then your stuck making multiple called forms if you need to use it more then once.
If you pass the data, you only need one copy of it.
Jim.
ASKER
Thanks for the guidance. Got it all working today.
Regards
Richard
Regards
Richard
If 2007 or higher you can use the TempVars collection. Add this to your click event:
TempVars.Add "lngJobID", Me.JobID
TempVars.Add "lngClientID", Me.ClientID
DoCmd.OpenForm "YourFormName", DataMode := acFormAdd
And in the popup form's open event, you can use the saved IDs:
Me.JobID = TempVars("lngJobID")
Me.JobID = TempVars("lngClientID")