Append query or something....

[tim.breeding]

Situation: I have a table (po_numbers) with 2 columns: po_number, po_used(boolean).

On a form I have a combo box which is populated by a short query

SELECT po_number
FROM po_table
WHERE po_used = False

After I select the PO number I'm trying to run an after_update event to change the selected po_number's po_used to 'true'.

This is what I've tried so far:

Private Sub po_number_AfterUpdate()

Dim strSQL As String

strSQL = "UPDATE po_numbers SET po_numbers.po_used =True" _
& " WHERE(((po_numbers.po_number)=" & [Forms]![po_req]![po_number] & "));"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

It's not working and I'm not that great with access. Someone suggested do and Append query to do the same thing, but I don't really know how to structure it.

Any suggestions?

 

[burrcm]

I think your main problem is that your setup is incorrect. It seems that you have created records for each order "in advance". I would not do this. Creating a new record for each new order seems to resolve the issue you are facing. Avoid using an autonumber primary key as your PO number as these cannot be relied upon to stay the same (e.g. if you delete a record then compact, the numbers after the deletion will change), rather do something like PO Number = Max([PO Number]) +1. This will ensure you don't have issues later.

Chris B

 

[ajetrumpet]

You can pretty much write the SQL that you already have....

UPDATE po_numbers SET po_numbers.po_used =True" _
& " WHERE(((po_numbers.po_number)=" & [Forms]![po_req]![po_number] & "

except it will look like...

UPDATE [table] SET po_used = "True"
WHERE[table].po_number = [Forms]![po_req]![po_number]

Write it into SQL view of the query. Don't know why it's in a module, what your doing would be a lot easier by query.

Also, I don't know what the "used" field is for, but if you're trying to elimate duplicate PO numbers like burrcm is implying, do a search on here for autonumbers(I'm assuming you're trying to use PO numbers that are alphanumeric in nature. You can make custom autonumbers...) I wouldn't think sequencing is important for autonumbers....but to some, it is...