Autopopulating a form

G

Gilrucht

Registered User.
Local time
Today, 14:30
Joined
Jun 5, 2005
Messages
132
I am using the following code to update my form, open a new form with a new record and then populate certain fields of that new record with data from the previous record:
-------------------------------
Dim strFrm as String

strFrm = "frm_Auto_Accidents"
'save pending edits
Me.Refresh
'open the "other" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ClientID = Me.ClientID
.CaseID = Me.CaseID
End With
-------------------------------------------
This code works perfectly. Here is my problem. I also need to pull data from one field which is not on the 1st form(Its in a different table) into a unbound textbox. Is there a few lines of code I can add that will pull the data from the table. The link is not a problem because my tables are linked on client id. I know I can use a query but the whole point of this code was to eliminate the need for a combobox.
Can anyone help?
 
Why do you think queries require a ComboBox?
 
I assume the unbound field to be populated is on the strFrm. Here's one idea extending the last bit of your code...
Code: 
With Forms(strFrm)
  'and set the values of any of its controls
  .ClientID = Me.ClientID
  .CaseID = Me.CaseID
  .tbUnbound = DLookup("SomeField", "SomeTable", "SomeCriteria = " & Me.SomeLocalData)
End With
If that's not totally clear, check "DLookup" in Access and/or VBA help.
 
Rural.

I don't. Its more that I am not a professional developer and the only querys I have worked with are in comboboxes.

Lag, thank you. Here is what I came up with .

-------------------------------------------
With Forms(strFrm)
'and set the values of any of its controls
.ClientID = Me.ClientID
.CaseID = Me.CaseID
.txt35 = DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Me.ClientID)
End With
---------------------------------------------

BTW, did you recognize the code? You suggested it to me in another post. Thats twice now you have really helped me out. Its greatly apprciated.
 
Thanks for responding. If you don't mind an observation from a stranger, it seems as though you are yanking the Forms(strFrm) around a lot from wherever this code is running when the form could probably accomplish everything on its own if you simply passed it the info in OpenArgs.

I suspect if you, or someone else, looks at whatever form Forms(strFrm) is pointing to 5 years from now, it will take a while to figure out how the heck ClientID, CaseID and txt35 get populated.

Programmers enjoy elegant code but clever code, while interesting to view and figure out, can get you into trouble. Maintainability is a major consideration when developing software.

Just my $0.02 and worth every penny you paid for it! 8^)
 
Rural,

I never mind advice. I need all the help I can get. I asked for help in passing values to my new form and this was the code which was suggested. I'm not sure if it would make a difference but I only posted the part of the code relevant to this question. This code is actually part of a select case statement bound to the after update event of a combo box. All together there are approximately 25 seperate cases(ie forms) that this code is attached to. Heres a sample of 1 complete case statement:

-------------
Private Sub cbo5_AfterUpdate()
Select Case Me.cbo5
Dim strFrm As String
strFrm = "frm_StateCriminalCaseInfo"

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

'save pending edits
Me.Refresh

'open the "other" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.SCRClientID = Me.CaseClientID
.SCRFileNo = Me.CaseFileNo
.txt35 = DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Me.ClientID)
End With
End If
'Start next Case Statement
Case

-------------------
The code would then repeat with the next form nameuntil there was a case statement for all 25 forms.
I don't know a lot about the "OpenArg" statement but as I understand it I would still need my 25 case statements to open each form depending on the selection made in the combobox. Does the openarg format still make sense in that context?
 
Hi Gilrucht,

Absolutely! Here's a sample of what I think it could look like (maybe):

--- Warning - all code is untested AirCode ---
Private Sub cbo5_AfterUpdate()
Select Case Me.cbo5
Dim strFrm As String
Dim strParameters As String
strFrm = "frm_StateCriminalCaseInfo"

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

'save pending edits
Me.Refresh

strParameters = Me.CaseClientID & ";"
strParameters = strParameters & Me.CaseFileNo & ";"
strParameters = strParameters & "#" & _
DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Me.ClientID) & _
"#" '-- If you want to pass a date
'-- OR maybe
strParameters = strParameters & Me.ClientID

' Then the form open would be:
DoCmd.OpenForm strFrm, , , , , , strParameters
'-- That's it for this case - the other form does everything
End If
'Start next Case Statement
Case


-------------------------------------------
And here's the receiving form's Load event:
--------------------------------------------
Private Sub Form_Load()

Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing parameters
DoCmd.GoToRecord , , acNewRec '-- Calling form needs a new record
Args = Split(Me.OpenArgs, ";")
Me.SCRClientID = Args(0)
Me.SCRFileNo = Args(1)
Me.txt35 = Args(2)
'-- Or maybe something like:
Me.txt35 = DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Args(2))
'-- If you just pass the ClientID and let this form do the DLookup (maybe)
End If

End Sub
------------------------
 
You could open the other form like so:
DoCmd.OpenForm strFrm, , , , , acDialog, strParameters

If you wanted the current form to wait right here until the the other form was complete.
 
Thank you very much. I really appreciate it. You don't realize how much help you've been This is a db fot my law office and every form is linked on case and client id. When a new client comes in there are maybe 15 or so forms to be opened filled in. It makes life easy to have code like this when you are opening up a series of new records with the same two fields
 
You are very welcome. This Forum is full of answers if you just ask and are a little patient. :D
 
Rural,
I couldn't get your code to work after I plugged it in. I kept getting an error message that I was missing a statement in the select case arguement. I tried it with just one form and no case statement. I put the OpenArgs statement on the onload event of the new form. It compiled with no errors and opened the new form but didnt pass the values. I played with it all afternoon and couldn't get anywhere. I appreciate all your help but Lagbolts code has been working for with me and his suggested modification works as well. I think I'm better off sticking with something I have working than continuing to play with something I can't seem to get right. I do appreciate your time and effort though.
 
You give up too easy. Do you IM? I am RuralGuy9999 on Yahoo Messanger. If not, could you post the particular Case Statement down to the next Case Statement that was throwing the error? You can also send email to my public persona on this Forum.
 
Last edited:
here is the code. I was using it in the after update event of a list box because there are only 4 forms in this one. If I get this one working the next one has 25 and I will use a combo box for that one. Please note the name fields are unbound text boxes, (just to let the user the name of client she is working on)Only the clientID and fileid are formfields bound to a table.

------------
Select Case Me.Lst50
Dim strFrm As String
strFrm = "frm_StateCivilCaseInfo"

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

'save pending edits
Me.Refresh

strParameters = Me.CaseClientID & ";"
strParameters = strParameters & Me. CaseFileNo
strParameters = strParameters & Me.FName & ";"
strParameters = strParameters & Me.MidInit & ";"
strParameters = strParameters & Me.LName & ";"
strParameters = strParameters & Me.Suffix & ";"
' Then the form open would be:
DoCmd.OpenForm strFrm, , , , , , strParameters
'-- That's it for this case - the other form does everything



End If

Case "State Criminal Cases"

strFrm = "frm_StateCriminalCaseInfo"

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

'save pending edits
Me.Refresh

strParameters = Me.CaseClientID & ";"
strParameters = strParameters & Me. CaseFileNo
strParameters = strParameters & Me.FName & ";"
strParameters = strParameters & Me.MidInit & ";"
strParameters = strParameters & Me.LName & ";"
strParameters = strParameters & Me.Suffix & ";"
' Then the form open would be:
DoCmd.OpenForm strFrm, , , , , , strParameters
'-- That's it for this case - the other form does everything



End If

Case "Federal Civil Cases"

strFrm = "frm_FederalCivilCaseInfo"

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

'save pending edits
Me.Refresh

strParameters = Me.CaseClientID & ";"
strParameters = strParameters & Me. CaseFileNo
strParameters = strParameters & Me.FName & ";"
strParameters = strParameters & Me.MidInit & ";"
strParameters = strParameters & Me.LName & ";"
strParameters = strParameters & Me.Suffix & ";"
' Then the form open would be:
DoCmd.OpenForm strFrm, , , , , , strParameters
'-- That's it for this case - the other form does everything



End If

Case "Federal Criminal Cases"

strFrm = "frm_FederalCriminalCaseInfo"

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else

'save pending edits
Me.Refresh

strParameters = Me.CaseClientID & ";"
strParameters = strParameters & Me. CaseFileNo
strParameters = strParameters & Me.FName & ";"
strParameters = strParameters & Me.MidInit & ";"
strParameters = strParameters & Me.LName & ";"
strParameters = strParameters & Me.Suffix & ";"
' Then the form open would be:
DoCmd.OpenForm strFrm, , , , , , strParameters
'-- That's it for this case - the other form does everything



End If
End Select

End Sub
 
Hi and thanks for the code post.

How come the following code seems to appear twice under each Case Statement?
------------------------------------------------
If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else
-------------------------------------------------

At 1st glance I think that should toss an error when you compile. At the very least you should get the MsgBox twice!

I'll keep looking while you respond. :)
 
you are right. It shouldn't appear twice. thats what happens when you are cutting and posting and trying to put things in the right place. will fix that and try to compile again
 
Two things: no ";" on the last strParameters = strParameters & TheFieldOfYourChoice

Put this code in the Case Statement you want to test just before the call for the form:
MsgBox "strParameters =" & vbCrLf & vbCrLf & "[" & strParameters & "]"
DoCmd.OpenForm strFrm, , , , , , strParameters

What version of Access and what OS?

Run the test Case and see if the string looks OK.
 
Last edited:
well taking out the duplicate msgbox allowed it to compile and now it opens the new form. now I have to get it to pass the correct values
 
We could also put the Diagnostic MsgBox in the receiving form.

Except it would look like:

'-- Form is being opened from a form passing parameters
MsgBox "OpenArgs =" & vbCrLf & vbCrLf & "[" & Me.OpenArgs & "]"
DoCmd.GoToRecord , , acNewRec '-- Calling form needs a new record

You know that we are going to Dirty the New Form don't you?
 
Last edited:
I have it working with your original code minus the duplicate msg box. I had a mistake on one of the arguements in the onload event. I use SCV,SCR,FCV & FCR in front of client and fileid to distinguish them in each of the 4 forms-(StateCivil[SCVCLIENTID]), StateCrimina([SCRCLIENTID]), (FederalCriminal [FCRCLIENTID])and (Federal Civil[FCRCLIENTID]). Instead of copying SCVClientID I had copied SCRClientID. Once I fixed that it is passing the values as it is suposed to. Since it is working as you originally coded it is there any reason to add the changes you suggested? As for dirtying the form, I'm vaguely aware of what that means. I don't see it as any big problem.
I was actually looking for code awhile ago that would populate then clear a random field on a new record so as to set my client number on the main form when the record opened. Miles had given me the code a year ago but I lost it. So I'm not worried about dirtying the record.
 
Outstanding! :D No reason to try any diagnostic code...you have it working!

Dirty, if you didn't know, means the NewForm will want to save the data we entered even though the user has not touched the form yet. It is generally a User Interface (GUI) violation. The user would like to feel the system works the same everywhere. If elsewhere on forms they can exit without the system complaining about "Loosing data" then that is the way this NewForm should work.

It is a fine point and your call. It is *your* system after all! ;)
 
You must log in or register to reply here.

Similar threads

J
Solved Troubleshooting Subform
Replies
10
Views
398
jamesave
J
A
Open a form and insert data SQL
Replies
6
Views
595
andymartin3186
A
Gigitty
Never seen this error linking a form
Replies
8
Views
382
Gigitty
Gigitty
A
Form with subform in transaction
2
Replies
39
Views
1,655
Albert D. Kallal
A
A
“Add A New Record” function is not working
Replies
12
Views
1,173
access2010
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom