Print one record and then delete it? 2

[Turb]

Hi All!
I have a form that I have created that will print out onto label stock.
This form's record source is a table in the Db that contains a series of MAC addresses.
This is where I run into problems...
I can't seen to figure out how to make just the 'top' (sorted ascending) mac address (a single record) show in the form's text box.
I mean, it does that, but if a user selects the text box and hits the page down key, he/she can scroll through all the addresses in the table (I need to print these in a specific order).
I also need to be able to underlay the 'print' command button in the form header with code that would, once the form printed, delete that mac address from the table (or some similar function so that a single mac address cannot be printed twice).
Can anyone help?

 

[Remou]

Have you considered

SELECT TOP 1 ...

It is probably best to have a deleted flag or a printed date. Use a message box or form to ask the user if the print is ok, and if it is, mark the record(s) appropriately.

 

[acent]

Another way to do it is the cycle property of the form. If memory serves, "all records" is the default. Change that to "current record".

Hope this helps.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[Remou]

acent
The cycle property prevents the tab key and other control-to-control keys moving to the next record, it does not stop page up and page down keys.

 

[acent]

oh.... dang.... Yeah go with the SELECT TOP 1 sql then.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[Turb]

Gosh!
Thank you both for your ideas!

To be sure I understand...
Instead of using the table as a record source for the form, I should use a SQL select query that will retrieve a single (SELECT TOP 1) record to populate the form.

But how do I set "a deleted flag" or "mark the record appropriately"? Can I run a If/Then/Else statement under the VB "ok" button that would close the form and delete the "TOP 1" record (maybe using the same, or same type, of SQL query)?

 

[Remou]

You can certainly delete the record, however, I would generally advise against deleting records.

You could add some code to the print button, say:

If MsgBox("Ok?") Then
   Me.chkPrintOK=True
   Me.RecordSource="SELECT TOP 1 ID, Blah FROM tblTable WHERE chkPrintOK=False ORDER BY Blah"
End If

It would also be possible to delete:

strSQL="DELETE FROM tblTable WHERE ID = " & Me.ID
CurrentDB.Execute strSQL, dbFailOnError

 

[Turb]

Remou,
Thank you for the prompt response!

Ok, so I shouldn't delete any records, gotcha!
But wouldn't this

SELECT TOP 1 ID, Blah FROM tblTable

keep me just checking the 'Blah' record ID everytime?
Also, I don't understand how the code will 'mark' the record if the print is 'ok'? Should I create another field in the table where the SQL can fill in a number 1 or something and alter the original SELECT SQL query to "SELECT TOP 1 WHERE ## IS NULL"?

 

[Remou]

A field that indicates that a record is deleted and/or printed is often to be preferred, in your case a print date might be best, but you could use a flag, as illustrated in my post dated 17 Sep 07 8:18.

 

[Turb]

Remou,
Thank you!

Star for you!