Question How to launch a MsgBox from a table making query

[Bill Pugh]

I've go a 'make table query' that makes a table called [Certs Data]:[QC_Status_Code]. When this query runs I would like it to look at the [QC_Status_Code] field and IF there are any with a 'H' I would like to launch a MsgBox telling the user there is material on HOLD.

This is probably simple to do but I just can't seem to get it to work

Thanks!

 

[jzwp22]

A query by itself will just run it cannot return any messages. Also, a query will not analyze each record, to do that you would have to open a recordset based on the query in code loop through the records to see if any match the criteria. If so, you can return the message for each record or wait until all have been processed and then return the message.

By the way, you should only need to run the make table query once. After that you should be just appending new records. Out of curiosity, if the data already exists elsewhere in your database why would you need to make another table anyways?

 

[Bill Pugh]

How about if I show the QC_Status_Code on a sub-form? How can I launch a MsgBox then? I have know idea how to write the code to do this

 

[jzwp22]

Assuming the data that has the code exists in a table in your database you can have an unbound form with a textbox control that shows the # of records that have the QC status code. You would use the DCount() function in the textbox's control source to do this. There would be no need for VBA code.

BTW, you did not answer my question:
Out of curiosity, if the data already exists elsewhere in your database why would you need to make another table anyways?

 

[Bill Pugh]

I have know idea why this was made this way. The only thing I wanted to do is have a popup message box if a field in a table had a 'H' in it. This is all I wanted to do

 

[jzwp22]

You can find all the records with an H in it by using a SELECT query. I'm a little confused as to why your original post made reference to a Make Table query--that is totally different.


Perhaps a more detailed explanation would help us better understand your database and what you are trying to do with it.

 

[Bill Pugh]

Pretty easy to follow what I need. I just don't know how to do it.

The only thing I wanted to do is have a popup message box (MsgBox) if a field in a table has a 'H' in it.

 

[jzwp22]

This is what the code might look like. How you trigger the code to run is another question.


IF DCount("*","tablename","QC_Status_Code=H")>0) THEN

msgbox "Material On Hold"

END