Solved

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

Posted on 2013-01-17
21
396 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 38790138
can i see your app? upload a copy of the db
0
 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now