How to pass value to child form and then store in database field

Hi. I am trying to pass a value to a child form and then store it in the underlying database.  I have a customer database, the customer will have more than one lab order.  The list of lab orders is a subform on the customer form in continuous form view with a command button beside each record to open that lab order.  So far so good.  I need to pass the lab order number to the lab samples form that opens up. (There will be several samples for each lab order so I need to group them by lab order #) I've used =[Forms]![frmLabOrder]![LabOrderNum] in the control source property of the text box.  That works fine but I also need to store that number in the Lab Samples table.  When I add a lab order the record won't save unless I bind the text box to the table field and manually type in the number.  Help anyone?
esbyrtAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
i have ask you the rules and you did not provide any response..

use this codes for the edit button

Private Sub cmdEditSample_Click()
DoCmd.OpenForm "frmSamplesQuery", , , "[SubmissionNo]=" & Me.SubmissionNo

End Sub
0
 
Rey Obrero (Capricorn1)Commented:
< I need to pass the lab order number to the lab samples form that opens up>

you can use the option OpenArg to pass the lab order number in the command that open the form

docmd.openform "YuorFormname",openargs:=me.LabOrderNum

in the open or load event of the form

me.textboxname=me.openargs
0
 
esbyrtAuthor Commented:
I added the docmd.openform and args to the command button in the lab order form and the rest to to load event of the lab samples form.  It isn't working.  The LabOrderNum in the samples form is bound to a field in the query as it needs to be stored.  Could that be the problem?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:
can i see your app? upload a copy of the db
0
 
esbyrtAuthor Commented:
I have attached the file.  Note that the field in question is actually SubmissionNo not laborder.  I just thought lab order would make more sense.
NBDC.accdb
0
 
Rey Obrero (Capricorn1)Commented:
what are the names of the forms involved?
0
 
esbyrtAuthor Commented:
frmSubmission has a command button to open the frmSamples Query.  I need the submission number at the top of the submission form to pass to the Submission Number field in the frmSamplesQuery.  I "could" have the user type the submission number but that leaves it open for more errors.  There can be many Samples per submission.
Thanks for having a look!
0
 
Rey Obrero (Capricorn1)Commented:
are you adding new records in form frmSamplesQuery ?
0
 
esbyrtAuthor Commented:
Yes, either edit or add new.
0
 
Rey Obrero (Capricorn1)Commented:
ok, when to add and when to edit..what is the rule?
0
 
esbyrtAuthor Commented:
Hm not sure I've thought it through that far.  My plan was to have the user click into the frmSamples to enter all the samples sent in with a submission, but they should be able to go back in to make changes as needed.  I do plan to set up a switchboard so if it's better to have add and edit separated that can be done.  I will have another form for entering the test results by sample number.
0
 
Rey Obrero (Capricorn1)Commented:
ok, to test the openargs option, use this codes

Private Sub cmdAddSample_Click()
DoCmd.OpenForm "frmSamplesQuery", OpenArgs:=Me.SubmissionNo

End Sub


in the open event of the form "frmSamplesQuery place this codes

private sub form_open(cancel as integer)

if me.openargs & ""<> "" then
   msgbox "The value pass to me is " & me.openargs
end if

end sub


you can use that in both the add or edit operation.
0
 
esbyrtAuthor Commented:
Okay that returns the correct value in a message.  Now how do I stick that value in the Submission Number field in the frmSamplesQuery?  That field is bound to the underlying SubmissionNo field in the query/table and I need it to save there.
Thanks!
0
 
Rey Obrero (Capricorn1)Commented:
are you editing or adding record?

if adding record,

if me.newrecord then
  me.SubmissionNo=me.openargs
end if
0
 
esbyrtAuthor Commented:
Okay do I need to keep any part of

if me.openargs & ""<> "" then
   msgbox "The value pass to me is " & me.openargs
end if

Of course I don't want the message popping up but when I comment out that section and add
 if me.newrecord then
  me.SubmissionNo=me.openargs
end if
in the On Open event I get a blank subform for the frmSamplesQuery where there are exising records that should show up

Thanks so much for taking the time to look at this!!
0
 
esbyrtAuthor Commented:
Oh and the submission number field stays blank even if I start adding a new record.
0
 
Rey Obrero (Capricorn1)Commented:
test this
NBDC-rev.accdb
0
 
esbyrtAuthor Commented:
That works perfectly for the add sample button but the edit shows all the samples, not filtered by submission number.
0
 
esbyrtAuthor Commented:
Sorry I guess I didn't understand what you were asking.  When the user opens the frmSamples from frmSubmission it should only show the sample records that match the submission number currently showing on the submission form.  It might be easier to follow if you start from the customer form, there is a command button beside each submission line, which opens the submission (details) form.  Then from there the user can add or edit samples that match the submission number.  I hope that's clear.
Thanks again.
0
 
Rey Obrero (Capricorn1)Commented:
<Sorry I guess I didn't understand what you were asking>

in the future, pay attention to the questions ask by the responding experts, they are important to understand your problem.

if you don't understand the question, clarify.

did you test the codes posted above for editing records?
0
 
esbyrtAuthor Commented:
Yes that did the trick.  Thanks again and sorry for the confusion. I have been working on multiple things at the same time - obviously not a good idea.
0
All Courses

From novice to tech pro — start learning today.