Solved

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

Posted on 2013-01-17
21
402 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

615 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