• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

Pass Parameter From One Query to Another

I have a form that uses fields from a query.  The query has a paramter prompt that asks for a job number.  The form has a control button that lauches another form (form #2).  Form2 reads data from a second query that also has a parameter prompt.  The parameter promt asks for the job number a 2nd time.  

Is there a way to pass the results of the 1st parameter prompt to the 2nd query?
 
0
mbrennan_pacbell_net
Asked:
mbrennan_pacbell_net
  • 9
  • 4
  • 3
  • +2
1 Solution
 
rockiroadsCommented:
if form1 is still open then your query may be able to reference the form value

I think format is something like

forms!form1name!fieldname
0
 
milduraitCommented:
I would be taking the parameters out of the queries, and using VBA to filter the recordsets.
.
Form1
Suggest you build a textbox,listbox or combo box allowing the user input the jobnumber, and then on it's after_update method using the docmd.applyfilter method to select the record in the form.

Form2
Suggest modify the onclick method of the button to set WhereClause parameter of the docmd.openform method.



0
 
mbrennan_pacbell_netAuthor Commented:
Form1
Suggest you build a textbox,listbox or combo box allowing the user input the jobnumber, and then on it's after_update method using the docmd.applyfilter method to select the record in the form.

I am a light user of Access.  I got the text box added.  I know where the After_Update property is.  How to I apply the docmd.applyfilter method?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Filters use SQL phrases --just like the Where clause of a query, but without "where"-- to filter records behind a form.

In the OnExit event of the  text box type

me.Filter = "JobType = '" & me.txtJobType & "'"
Me.FilterOn = True

Note that there is a single quote after the =, and another single quote at the end of the filter phrase;  these are required if you are using text ("Hard", "Easy", "Very Easy") in your text box.  If you are using a number in the filter --as you could with a Combo box-- your filter could be "JobType = " & me.cboJobType.
Paul
0
 
milduraitCommented:
Form 1
Paul has given you the correct syntax, and suggested the exit event, which could work well for you.
If your job number is of fixed length then you could also use the keyup event to fire your code, as per my example below. (I've called my textbox txtJobNo and I'm assuming that your job numbers have a fixed length of 6 characters.)


Private Sub txtJobNo_KeyUp(KeyCode As Integer, Shift As Integer)
 
    If Len(Nz(Me.txtSelect.Value, "")) = 6 Then
        me.FilterOn = "[JobNumberFieldName]=" & Chr(34) & Me.txtJobNo.value & Chr(34)
        me.FilterOn = true
    End If
 
End Sub

Open in new window

0
 
milduraitCommented:
Form 2
Your form will need a button, i'll call it cmdOpenSubForm.
Your code will be fired from the click event of the above button, and launch the subform populating the WhereClause property of the docmd.openform method.
Private Sub cmdOpenSubForm_Click()
 
   If Len(NZ(me.txtJobNo.value,""))=6 then
        docmd.openform("[SubFormName]",,,"[JobNumberFieldName]=" & Chr(34) & Me.txtJobNo.value & Chr(34))
   else
       call msgbox("Please enter a value for job number.") 
       me.txtJobNo.setFocus()
   End If
End Sub

Open in new window

0
 
milduraitCommented:
RockiRoads
With VBA you can do heaps more, like to test the job number against the database as it is keyed  and
enable/disable controls etc based on the outcome.  Suggest an investment in a learn VBA book would be a good one for you
Cheers
Mildura IT
0
 
rockiroadsCommented:
yes, thanks for the advice :)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
mildurait ....

ahhh, did you happen to NOTE ... where RockiRoads is ... on the MS Access Hall of Fame - OverAll  ?

mx
0
 
milduraitCommented:
mx: not too far behind me.

0
 
milduraitCommented:
just kidding... sorry rockiroads, i was meaning to reply to the author
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
lol ...
0
 
rockiroadsCommented:
lol, its cool. your solution is better, I went for the minimal approach.
0
 
rockiroadsCommented:
mildurait, I take it your an aussie? if so you gotta be into the cricket? I miss the cricket fights I had with flavo (another aussie). SA gonna beat Aus again, gonna be another great series.
0
 
milduraitCommented:
Too true, but sorry no fight, it's fantastic to see some competition for our team -- finally.
I did however receive a call from James Sutherland the other week, but unfortunately had to decline coming across - family commitments and all that.
0
 
milduraitCommented:
mbrennan
by the way, how are you going with your form and subforms?
never mind us, this is way attention grapping than facebook chat.

rockyroads
minimal is good, and thanks for the compliment.
0
 
mbrennan_pacbell_netAuthor Commented:
Rockiroads, call you contact me?

Mbrennan@pacbell.net
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
It's not a good idea to post your direct email here ... since any SPAM bot can easily (and most likely already has) harvest your email address.

mx
0
 
milduraitCommented:
RockiRoads
I don't think the author is going to choose your post as the accepted solution.
see: http://www.experts-exchange.com/Microsoft/Applications/Q_24151818.html?cid=238#a23679253 
;-)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 9
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now