Combo Box Issue

[jesusoneez]

I have a database (backend - just tables), that stores data for an asset register/call logging application. The frontend is pretty much finished, but I'm having one or two issues that are annoying me.

I have a form for logging requests...most combo boxes are filled in by looking at normalisation tables, such as cmbTechnician, cmbUser, cmbRequestType etc.

All this part works with no problems. There is an area on the form that looks at tblDesktops, tblLaptops and tblPrinters. When you use a combo box to pick a Desktop, other fields (such as make, model, serial number) are automatically filled in. Same goes for the Laptop combo box and Printer combo box.

This works fine. Note that these are NOT required fields in tblCallLog, as not all requests relate to a Desktop, Laptop or Printer. However, unless I pick an item from all three, the record will not save, producing the error;

"The Microsoft Jet database engine cannot find a record in the table 'tblPrinters/Laptops/Desktops' with key matching field(s) 'Printer/Laptop/Desktop'."

This has stumped me somewhat, as the combo boxes work fine if I select an item from them, telling me the relationship is working OK, so why it's insisting something has to be entered in these unrequired fields is beyond me.

Haylp!

EDIT: I've enclosed a picture of the relationships pertaining to this problem...these relationships were setup by the process of using "lookup" whilst setting up the tables. AssetID is an autonumber field, and I'm wondering if this is where the problem. All other primary keys are not autonumbers, as the text data kept within them is unique.

 

[Malcy]

Just a guess but it looks to me as if Jet is looking for a table called tblPrinters/Laptops/Desktops which suggest you have a bit of coding wrong.
Hope this isn't a red herring for you but think it would be worth looking at it since from what you suggest it should be looking in tblCallLog - or am I picking you up wrongly?

 

[jesusoneez]

Possibly. I just had a look and the source of frmCallLog is doing this;

tblDesktops_SerialNo, tblDesktops.HostName, tblDesktops.TagNoID AS tblDesktops_TagNoID FROM tblPrinters INNER JOIN (tblLaptops INNER JOIN (tblDesktops INNER JOIN tblCallLog ON tblDesktops.AssetID=tblCallLog.Desktop) ON tblLaptops.AssetID=tblCallLog.Laptop) ON tblPrinters.AssetID=tblCallLog.Printer;

Although I don't know what all the above actually means, the references are all right.

I'm assuming it's the bit in bold where my error kicks in.

I've tried changing the source to simply tblCallLog, and although this works fine, my text fields picking data up from other tables (such as the extra desktop/printer/laptop information) end up with #Name when I pick an item from cmbDesktop/Laptop/Printer.

I'm guessing the above is the SQL for the lookup to allow the extra information text fields to fill themselves.

Hmmmm...

(sorry if this double posts...the intial attempt ended with a 404 error).

 

[jesusoneez]

*bump*

If anyone can help with this I'd be most appreciative. I'll strip the database down and post an example if necessary.

Thanks.

 

[Malcy]

Not too sure of the SQL. You are getting #Name since the fields are not in tblCallLog.
I would be tempted to build a query to pull all the data you need for the combo then call if cboCallLog and redo the combo to take data from the query. I think this would probably be more efficient

 

[jesusoneez]

You're probably right...I think I too often trust Access's inbuilt way of doing things.

I'll give that a go (I was half thinking of starting the form again anyway).

Ta.