Union Query - Yes or No to search again

[m0aje]

Hello,

I am using the following union query to search serial numbers from 5 different tables.

SELECT[WORKSTATION].UnitModel,UnitPart,UnitSerial,PONum
FROM[WORKSTATION]
WHERE(((WORKSTATION].UnitSerial)=[Enter Serial No.]))
UNION ALL
SELECT[LAPTOP].UnitModel,UnitPart,UnitSerial,PONum
FROM[LAPTOP]
WHERE(((LAPTOP].UnitSerial)=[Enter Serial No.]))
UNION ALL
SELECT[SERVER].UnitModel,UnitPart,UnitSerial,PONum
FROM[SERVER]
WHERE(((SERVER].UnitSerial)=[Enter Serial No.]))
UNION ALL
SELECT[UPS].UnitModel,UnitPart,UnitSerial,PONum
FROM[UPS]
WHERE(((UPS].UnitSerial)=[Enter Serial No.]))

It works beautifully. However, in order to do a new search I have to close the query and open it again to run a new search.
How does one add a prompt in SQL to "Search another Serial Number" where a yes answer would re-run the query and a no answer would close it?

I would be most grateful for any help. THANK YOU!

 

[JHB]

..
How does one add a prompt in SQL to "Search another Serial Number" where a yes answer would re-run the query and a no answer would close it?

It is very simple - you can't do that in a query.

 

[jdraw]

Use a form. Include a button and a textbox.

textbox is for the "search term"
button click event modifies the search query sql behind the scene and runs the query

 

[m0aje]

Use a form. Include a button and a textbox.

textbox is for the "search term"
button click event modifies the search query sql behind the scene and runs the query

Thanks for your reply. I appreciate it.

 

[Simon_MT]

If you where using a Search Form with the Serial Number then From with the data could be opened with just one Where Condition.

Function Search_UnitSerial()
    With CodeContextObject
        DoCmd.OpenForm "Data Form", acNormal, "", "UnitSerial = '" & .[SearchValue] & "'", acFormEdit, acWindowNormal
    End With
End Function

Simon

 

[m0aje]

If you where using a Search Form with the Serial Number then From with the data could be opened with just one Where Condition.

Function Search_UnitSerial()
    With CodeContextObject
        DoCmd.OpenForm "Data Form", acNormal, "", "UnitSerial = '" & .[SearchValue] & "'", acFormEdit, acWindowNormal
    End With
End Function

Simon

Hello Simon,

Thank you very much for your reply. Would this code allow for searching a serial number from all the tables in the database? I have more than just the 4 tables in the database.
Again, thanks for your reply. I really appreciate it.

//m0aje//

 

[Minty]

To be honest your data model looks suspect to me. You should store all the products items in one table hand have a type field to identify them within that, then you would only be searching one serial number field.
At the moment when a new type arrives you have to add a new table and re-invent the wheel every time.

 

[m0aje]

To be honest your data model looks suspect to me. You should store all the products items in one table hand have a type field to identify them within that, then you would only be searching one serial number field.
At the moment when a new type arrives you have to add a new table and re-invent the wheel every time.

Hello Minty,

I agree with you that everything should have been put into one table. However, this is an old database that goes back to the year 2000 and it was done trial and error. It has progressed on from there so I am working with the circumstances that exist now as I cannot change the structure.
That's why the union query works great in the existing structure. That's why I thought some sort of 'prompt' to search again could be added somehow vice closing the query and starting again. Apparently it cannot.

Thank you for responding and for your comments. I appreciate it.

regards,

//m0aje//

 

[jdraw]

m0aje,

Watch this free video by Steve Bishop. The concept is what you're looking for. You may get ideas.

Since it is a form, the routine can be performed repeatedly. Add a new keyword, click the button...

 

[m0aje]

m0aje,

Watch this free video by Steve Bishop. The concept is what you're looking for. You may get ideas.

Since it is a form, the routine can be performed repeatedly. Add a new keyword, click the button...

Thanks Jdraw. I will give it a try.

Best regards,

//m0aje//