Access Database Issues, data not writing to table

[CHR1S]

I have a form where I have cascading combo boxes working, however when I move on to my next record the selected building and room although correct in the boxes does not save into the table.

 

Its going to be a stupid error where I have missed something obvious but I cant see the wood for the trees atm!

[Pashers]

Might be easiest if you upload the database, someone or myself might have time to have a quick glance and help resolve it?

[CHR1S]

combotest1.rar

 

So basically because I display the Building and Room real names and hide the BuildingID and RoomID I cant bind to them (Assets Form), so I will need some VB code on a save button to write those values to the Asset table in the required fields.

 

Does that look whats required?

 

Thanks

Edited July 2, 2012 by CHR1S

[CHR1S]

Think I have my head round it now, swapped the bindings to the primary keys, displayed 2 columns bit hid the first column with a 0cm width.

 

All working it seems!

[Pashers]

Glad you have it working, if you want the sql something along these lines would be a quick resolution:

DoCmd.RunSQL "INSERT INTO Assets ( AssetDescription, EmployeeID, StatusID, AssetCategoryID, Room, Building, VendorID, ModelNumber, SerialNumber, BarcodeNumber, DateAcquired, PurchasePrice, Comments, Description, [Date Disposed] ) SELECT '" & AssetDescription & "', " & EmployeeID & ", " & StatusID & ", " & AssetCategoryID & ", '" & Room & "', '" & cboCategories_2 & "', " & VendorID & ", '" & ModelNumber & "', '" & SerialNumber & "', '" & BarcodeNumber & "', '" & DateAcquired & "', '" & PurchasePrice & "', '" & Comments & "', '" & AssetDescription & "', '" & DateSold & "'"

 

You'll need to look at the tables again. The IDs in the Assets table should be Integer not text. The only primary key you should need is for the AssetID in that table. The rest are other 'ID' fields are called foreign and should link to a primary key in different tables.

 

For example in your Asset table you have EmployeeID. You also have Employee table with a field called EmployeeID. In the Employee table the EmployeeID field should be Primary Key. Not in the Asset table. Then you should link them together.

[CHR1S]

Thanks, tidied my tables and relationships up!

 

Just need to implement an audit trail and logon system and I think its ready... ish