Help - After Update ... I am stuck! (1 Viewer)

[andrewaldrich]

After a few days of hitting my against the wall, I could really use some help.

I have a pretty basic DB - 4 tables

Orders
Customers
Order Details
Products

Products are assigned to specific customers.

I have built a form with a subform. The Master Form contains ServiceID and CustomerID and Customer Name.

Linked to the Master is a subform - linked on ServiceID. The subform contains the product details including product id. The product id is in a combo box. When the user chooses a different customer (combo box), the available product id's change in the subform. Its all working splendidly until I read about After Update and VBA code.

I have little to NO experience with VBA code. I have managed to write some pretty hefty DB's without venturing into the unknown. Here's where I am with my code - its full of errors I know ... can someone help me please?

Private Sub PropertyID_AfterUpdate()
Dim productid As Variant
Form.Query1.productid = Null
Form.Query1.productid.Requery
Form.Query1.productid = Form.Query1.productid.ItemData(0)
End Sub

Thanks!

Andy

 

[Mihail]

What about a new description like this:

tblOrders
ID_Order - AutoNumber (PK)
OrderNumber - Text
OtherFields

tblCustomers
ID_Customer - AutoNumber (PK)
CustomerName - Text
OtherFields

tblProducts
......

tblOrderDetails
ID_OrderDetail - AutoNumber (PK)
ID_Order - Number (FK on tblOrders)
ID_Customer - Number (FK on tblCustomers)
......

I have a form based on the table ........ and a subform based on the table .....
My issue is ........

The Master Form contains ServiceID and CustomerID and Customer Name.

What is the red one ? How we can understand your DB if you don't show us ?

 

[andrewaldrich]

Mihail -

Thank you for your comments. I was brain dead last night and thought that I might be able to take a short cut in my posting. Obviously it was the end of the day ... attached is the database design. I hope this helps as I need to move on from this challenge.

 

[Mihail]

Yep. I saw.
Bad structure.

Remove all records from your DB.
Convert the DB in 2003 format (at least in 2007) Use Save as... for this.
Do a Compact and Repair
Upload the DB

I'll try to tweak your DB.