Solved

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

Posted on 2013-01-17
21
399 Views
Last Modified: 2013-01-17
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?
0
Comment
Question by:esbyrt
  • 11
  • 10
21 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38789839
< 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
 

Author Comment

by:esbyrt
ID: 38790129
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38790138
can i see your app? upload a copy of the db
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:esbyrt
ID: 38790175
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38790241
what are the names of the forms involved?
0
 

Author Comment

by:esbyrt
ID: 38790321
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38790326
are you adding new records in form frmSamplesQuery ?
0
 

Author Comment

by:esbyrt
ID: 38790391
Yes, either edit or add new.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38790405
ok, when to add and when to edit..what is the rule?
0
 

Author Comment

by:esbyrt
ID: 38790417
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38790427
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
 

Author Comment

by:esbyrt
ID: 38790452
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38790460
are you editing or adding record?

if adding record,

if me.newrecord then
  me.SubmissionNo=me.openargs
end if
0
 

Author Comment

by:esbyrt
ID: 38790488
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
 

Author Comment

by:esbyrt
ID: 38790489
Oh and the submission number field stays blank even if I start adding a new record.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38790510
test this
NBDC-rev.accdb
0
 

Author Comment

by:esbyrt
ID: 38790518
That works perfectly for the add sample button but the edit shows all the samples, not filtered by submission number.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38790524
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
 

Author Comment

by:esbyrt
ID: 38790539
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38790546
<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
 

Author Comment

by:esbyrt
ID: 38790648
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
table joins in qry 17 61
Copied Report Does not run on a different Query Source ? 7 32
Sum in Split Form 17 27
SQL Select in Access 2003 3 23
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

809 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