Confirm that records are empty before running update sql

[snipe]

Hey guys,

I have a form (pictured) that is used to issue sales worksheets to our salespeople.

When either of the "Issue.." buttons is pressed, the follow code runs (along with some other stuff that doesn't matter to you guys):

 CurrentDb.Execute "UPDATE T_checkout_master set store=" & Me.SelectStore & ", Salesperson='" & _
Me.EmpCode & "', DateIssued=#" & Me.IssueDate & "#, SalesName='" & Me.SalesName & "' WHERE SerialNumber BETWEEN " & _
Me.SerialFrom & " AND " & Me.SerialTo & ""

What is the best way to ensure that the sql is only update records that are empty (where DateIssued is null)? Theoretically this shouldn't be an issue, but 1 store already overwrote another stores entries due to a keying mistake.

I am assuming that I can just put the DateIssued is null in that code after the WHERE part, but how would I make sure that none got updated unless all are able to updated. All or nothing. I also need to display a msgbox.

Thanks

[ssanfu]

Since you are using VBA code, you could execute a Select query first...?

Something like:

sSQL = "Select DateIssued
sSQL = sSQL & " FROM T_checkout_master"
sSQL = sSQL & " WHERE Store = " & Me.SelectStore
sSQL = sSQL & " And SerialNumber BETWEEN " & Me.SerialFrom & " AND " & Me.SerialTo
sSQL = sSQL & " And DateIssued IS NULL"

'open the recordset

'Check for records
If rst.bof and rst.eof then
' no numbers issued
' so execute the update query
else
'display error message
'exit sub/function
End if



-----
I have a question. This has bit me before.
You have starting number 12356 and ending number 12360. # of worksheets = 4

But I think the number of worksheets should be 5 .
123456 = 1 worksheet
123457 = 2 worksheets
123458 = 3 worksheets
123459 = 4 worksheets
123460 = 5 worksheets

You are using the BETWEEN keyword, so that is inclusive of the start and end numbers..

Just curious.......is the number of worksheets important?

[snipe]

I have a question. This has bit me before.
You have starting number 12356 and ending number 12360. # of worksheets = 4

But I think the number of worksheets should be 5 .
123456 = 1 worksheet
123457 = 2 worksheets
123458 = 3 worksheets
123459 = 4 worksheets
123460 = 5 worksheets

You are using the BETWEEN keyword, so that is inclusive of the start and end numbers..

Just curious.......is the number of worksheets important?

Not super important, but needs to be right nonetheless. That field was just the End number minus the Start number. It is just for reference for the user. Appreciate you bringing it to my attention.

sSQL = "Select DateIssued
sSQL = sSQL & " FROM T_checkout_master"
sSQL = sSQL & " WHERE Store = " & Me.SelectStore
sSQL = sSQL & " And SerialNumber BETWEEN " & Me.SerialFrom & " AND " & Me.SerialTo
sSQL = sSQL & " And DateIssued IS NULL"

'open the recordset

'Check for records
If rst.bof and rst.eof then
' no numbers issued
' so execute the update query
else
'display error message
'exit sub/function
End if

Could you give me a bit more detail on that last part? You lost me after you built the SQL statement.

[NTC]

I am not a big fan of writing sql in vba unless it offers an advantage in flexibility; which I don't see in a form like this....having a query object is a lot easier to trouble shoot because you can run it stand alone and experiment with it. So while it is not the question you asked - but my recommendation is to create an Update query object - - let's call it CheckOutUpdater" - - and change the vba to simply run the query i.e. docmd.openquery "CheckOutUpdater". It is a simple change and in that new query it is very easy to add the criteria of DateIssued to be Is Null.

Actually regardless of whether or not you go with a new Update query object - if "T_checkout_master" is a query object - then you could add the criteria of DateIssued to be Is Null to that query......

[snipe]

"T_checkout_master" is a table.

The main thing I need is for the program to check all of the records that the user is trying to update to make sure that they are ALL empty. They method you (NTC) described, won't overwrite used records, but it won't notify the user that not all were updated.

See what I'm saying?

[ssanfu]

@NTC, I agree with you, but the OP seems to know VBA somewhat. Since he posted the question with VBA, I replied with a VBA answer.
I frequently create saved queries to determine if I am on the right track or to troubleshoot a query. Once I know the query is doing what is it supposed to do (actually , what I want it to do), then I will convert it to VBA SQL.

@snipe,

Could you give me a bit more detail on that last part? You lost me after you built the SQL statement.

I was trying to give some steps in pseudo code..... apparently not very well.

So, the VBA SQL would open a recordset with the criteria. Then you have to determine if there are any records returned. If the beginning of file (BOF) pointer is true AND the end of file (EOF) pointer is true, then there are no records in the record set. Therefore, "DateIssued" is null for all of the serial numbers and store number. It would be OK to execute the action query to issue the worksheets.

If the BOF or the EOF or both are false, then there are records returned and *some* of the serial numbers have been issued for that store. So use the msgbox function to give an alert. Don't know what you would do after this.

Don't forget to close the record set and destroy the declaration
( r.Close
Set r = Nothing)

I am assuming that I can just put the DateIssued is null in that code after the WHERE part, but how would I make sure that none got updated unless all are able to updated. All or nothing. I also need to display a msgbox.

This was just me thinking through the steps that might be required. Maybe also a message box if the serial numbers are issued?

Clear as mud??

[NTC]

the concept of 'make sure they are all empty' is not applicable when one is working in record sets - which is the fundamental approach in database design and a key differentiator from a programmatic approach which thinks in terms of linear looping...... when you have a query by which the criteria of a field is that it is null - - then by definition the records that this query works with are null... there is no such thing/need as 'making sure'......

in terms of the human experience - if one is attempting to preview/prevent for them the legitimacy of a field's value during their work with a form - - one can trigger a Dlookup method to check/compare values while they are working in a form; but that did not seem to be what your post was about. My impression was that you wanted to apply an update to a set of records while being sure it was only being applied against those with a null in a key field.