Solved

Passing data from one form to create a record in another form

Posted on 2013-01-17
13
498 Views
Last Modified: 2013-01-21
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
0
Comment
Question by:rltomalin
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 38786350
In general terms...

docmd.OpenForm "formname",,,,acFormAdd
Forms!formname!controlname= "textvalueInquotes"
Forms!formname!controlname= numbervalueNoquotes
Forms!formname!controlname= #datevalueInOctothorpes#
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38786355
What version of Access are you using?

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")
0
 

Author Comment

by:rltomalin
ID: 38786774
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",,,,acFormAdd
Forms!frmNewClientLog!LogType= "Initial Evaluation"  << setting the value to a constant
Forms!frmNewClientLog!JobID= #JobID#   << setting the value to a value from the main form
Forms!frmNewClientLog!ClientID= #ClientID#

Regards

Richard

Picking up the field values from the current form by putting them inside # chars?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38786892
NO.

Only date VALUES go inside # #     (as in  #2013-12-30#)
numbers have no  quotes nor #
Text has " "
0
 

Author Comment

by:rltomalin
ID: 38787000
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
0
 
LVL 57
ID: 38787184
<<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=txtCustomerID:" & 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(varOpenArgs, "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.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 61

Expert Comment

by:mbizup
ID: 38787194
<<
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).
0
 

Author Comment

by:rltomalin
ID: 38788122
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",,,,acFormAdd
Forms!frmNewClientLog!LogType= "Initial Evaluation"  << setting the value to a constant

Forms!frmNewClientLog!JobID = Me.JobID  
Forms!frmNewClientLog!ClientID = Me.ClientID

Regards
Richard
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38788144
The code you have just posted should work, provided all the names  and data types are correct
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 38788161
Have you tried that code exactly as you posted it??

It looks like your syntax is perfect!
0
 

Author Comment

by:rltomalin
ID: 38788257
Thanks guys
Unfortunately I am away from work now until Monday.

So will leave this for now and I will continue next week.

Regards
0
 
LVL 57
ID: 38788563
<<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.
0
 

Author Closing Comment

by:rltomalin
ID: 38801496
Thanks for the guidance.  Got it all working today.

Regards

Richard
0

Featured Post

New! My Passport Wireless Pro Wi-Fi Mobile Storage

Portable wireless storage to offload, edit, and stream anywhere.

High-capacity, wireless mobile storage designed to accompany professional photographers and videographers in the field to easily offload, edit and stream captured photos and high-definition videos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’m often asked about newer and larger USB drives connected to SBS2008 and 2011 failing Windows Server Backup vs the older USB drives not failing. As disk space continues to grow and drive technology change SBS2008 and some SBS2011 end up with the f…
If you are a user of the discontinued Microsoft Office Accounting 2008 (MSOA) and have to move to a new computer running Windows 8, you will be unhappy to discover that it won't install.  In particular, Microsoft SQL Server 2005 Express Edition (SSE…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now