Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-17
21
Medium Priority
?
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

704 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