managing serialized items

[Trevor Howard]

I have a products table, all items are listed by serial number (primary key). I have a query which filters out sold items, leaving me products in stock.

My difficulty starts when I select via a combo, an item from the Stock query to an Order Detail subform, this works till I add a sold date to the item in the subform, after closing and opening, that item has disappeared from my subform, and has been flagged sold in my products table.

I have searched the forum for some ideas but nothing on managing serialized items. I have been working on this for an age, I know it is my lack of experience. Has anyone got any ideas or samples?
Thanks for any ideas

 

[Trevor Howard]

Please HELP!!

 

[Rich]

What exactly is the problem? From the info you've supplied, once a date sold has been entered the item has been flagged as sold. What would you expect it to be flagged as, do you not want the item flagged, or can there be multiple lines per item? Do you still want to see the item on the subform?

 

[Trevor Howard]

Thanks Rich

I am trying to select items not sold (sold date in Products table) from my Products table with a query (Stock (Q)), all items are one off, and are serial numbered, no multiples.

In Delivery form I want a subform Order Details, which with a combo selects items from my stock query. The item once selected is to be marked sold (todays date) in the products table so it cant be sold again.

My difficulty is that the item is then “dropped” from my query once a sold date is added. How can I get round updating my products table but then not loss the item from my subform?

Maybe I need a different approach to the whole problem. Any help will be brilliant. I have sized down my db to attach.

 

[Rich]

Please post a 97ver of the db

 

[Trevor Howard]

Hello Rich

DB in 97. Thank you again.

 

[Trevor Howard]

Would a DLookup get me round this problem? I have to get something done by the end of the week

 

[Trevor Howard]

Thanks Pat,

I have gone with option .1, but I cant get code to work in the “BeforeUpdate event of the combo to prevent someone from selecting something already sold” could you help me with this code???

I have also found if an item is selected from the combo, but then the record needs to be deleted, i.e if it was selected by mistake, the “sold” date is not removed from the products table when the following “OnClick” is run, how can I alter the delete code to also clear the date field in products table at the same time, so making item available again?

Private Sub Delete_Click()
On Error GoTo Err_Delete_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Delete_Click:
Exit Sub
Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click

VBA is beyond me, so would appreciate your help once again! Thanks!

 

[Trevor Howard]

Pat,

Thank you so much!

Yes it was a wizard, I used a command button. I thought the code looked a bit odd.

Thanks again

 

[Trevor Howard]

Pat,

I have tried your before update code,

Private Sub Order_Details_ProductsID_BeforeUpdate(Cancel As Integer)
If Me.Order_Details_ProductsID.Column(4) = Date Then
MsgBox "You cannot select this item. It is already sold.", vbOKOnly
Me.Order_Details_ProductsID.Undo
Cancel = True
End If

End Sub

I cant get it to work if I have a sold indicator in shortdate format, it works if I change it to text, but I would like it in date format.

I have tried "dd/mm/yyyy" in place of "Date" any idea?
Thanks again!