how to capture the input data for a query of a form and pass it to vba code

[dai_lo]

Hi,

I have a form which RECORD SOURCE (you can find it in the form design) consists the following query :

SELECT DOP_new.[Barcode #], DOP_new.[Deal Name], DOP_new.[Client Name], DOP_new.[Reference #], DOP_new.[Document Date], DOP_new.[Document Type], DOP_new.[Number of Documents], DOP_new.[Additional Comments], DOP_new.[From Date], DOP_new.[To Date], DOP_new.[Box #], DOP_new.[Of #], [tbl_Core Non-Core].[File Type], [tbl_Core Non-Core].Scan, DOP_new.[TD Account #], DOP_new.[Printed By], FormatDateTime([Please Date of Shipment (mm/dd/yyyy)]) AS DOS
FROM [tbl_Core Non-Core] INNER JOIN DOP_new ON [tbl_Core Non-Core].[Document Type] = DOP_new.[Document Type]
WHERE (((DOP_new.[Barcode #])=Format([Please re-enter bar code #],"0000")) AND (([tbl_Core Non-Core].[File Type])="Security") AND ((DOP_new.[Date of Shipment]) Is Null))
ORDER BY DOP_new.[Deal Name];

the above query will extract data based on the entered information, Barcode # and DoS

I would like to check if the entered Barcode # and DoS are valid
before the form opens.

what i mean by valid is

Barcode # does exist in the database.

DoS is a valid date and must be >= current date.

please advise

thanks

Alice

 

[ajetrumpet]

Well,

First of all, to get the criteria prompts, the person is going to have to initiate the opening of the form, or else they won't even get the chance to enter the data you want to evaluate.

Second, if you do it this way in order to check the validity of the input values, and put conditional statements on any "initiation" event of the form "activation" process, it probably won't work, because the form will still be "activating / loading".

I would say: Keep the query as the recordSouce, BUT... Use a switchboard form or something like that to conditionalize the opening of the form (with input boxes that prompt the user to enter the values you want before the form opens). This way, if the input values that you look up are not in the database, you can stop the opening of the form before it even begins. Would this be easier, I wonder? Regardless of what method you use, you probably want to use either FindFirst or DLookup to check your values' presence in the tables.