Using same parameter in two queries 1

[doctorswamp]

Hi

Have an orders form that lists customer details, with order items in a subform. The items may come from different suppliers.

I want to select the items for a particular supplier, generate a report, and flag these items as ordered, ie with subform fields Supplier and Ordered, select all items for supplier XYZ then update Ordered to Yes for just these.

Doing this with select and update queries is fine.

I'd like now to be asked for the Supplier name instead of having it as a fixed criterion, then feed this into the select query for the report and the update query for the flag.

Thanks

 

[danvlas]

What happens if you set the parameters to have the same name?


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[doctorswamp]

Thanks Daniel

Just so the question's clear, the subform items may look like this for any particular order number.

Item Qty Supplier ordered
------------------------------------
cup 2 ABC
saucer 3 XYZ
spoon 2 ABC
plate 2 ABC
knife 3 XYZ

I want a command button that says 'Pick Supplier', then launches a report for say ABC. Then ticks the ordered field for the two entries.

 

[TheAceMan1]

How are ya doctorswamp . . .

Realize, with a record containing the desired supplier selected all you need to do is [purple]set the criteria for your queries to the control on the form[/purple]:

[blue]Where ([Supplier] = '" & Forms!FormName!Supplier & "')"[/blue]

With thw queries now intact the button in the code would look like:

[blue]   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   Msg = "Print report with '" & Me!Supplier & "'?" & DL & _
         "Click 'Yes' to print." & DL & _
         "Click 'No' to abort . . ."
   Style = vbQuestion + vbYesNo
   Title = "Verify Supplier to Print! . . ."
   
   If MsgBox(Msg, Style, Title) = vbYes Then
      [green]'Open Report
      'Update Ordered[/green]
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[doctorswamp]

Hiya AceMan

Top advice as ever!

I've adapted your suggestion a bit. In case it wasn't clear the main form has customer details and the subform the order item details and their suppliers.

The supplier field in the subform now has an On Click procedure to run the report that generates the order. The report uses a query that takes the supplier name from the subform as you suggested. Then an update query flags these items. Job done!

Next thing is to save the report in some format suitable for storing and emailing.

Many thanks for your help.