I've been lurking on your site for a while now and have found numerous solutions to problems here, but I find myself in a situation where I need some advice, before I go prematurely bald. I do have a reasonably good understanding of relational databases, but I am fairly new to building them. Apologies if I miss any pertinent information.
I have a database that stores Contact's (Customers, Engineers, Suppliers) details in one table (with a [Category] field to differentiate them). In another table, I have subscription card details.
On the main Contact details form there is a combo box that allows me to allocate a subscription card to a contact. It pulls the data from a query called qryUnassignedSCards which works thus:
Expand|Select|Wrap|Line Numbers
- SELECT SkyCards.[Sky Card Number], SkyCards.[Ref Number], SkyCards.Engineer, SkyCards.[Sky Card ID]
- FROM SkyCards LEFT JOIN Contacts ON SkyCards.[Sky Card ID] = Contacts.[Sky Card ID]
- WHERE (((Contacts.[Sky Card ID]) Is Null))
- ORDER BY SkyCards.[Sky Card ID] DESC;
Now the problems:
1) EDIT: NOW RESOLVED The combo box displays the engineer's [Contact ID] as opposed to the name. The query results show the name: [First Name]&" "&[Last Name] presumably because that is how qryEngineers (which is the RowSource for SkyCards.[Engineer] in frmSkyCardDetails) is set up (it is bound to qryEngineers.[Contact ID]). I've not spent a lot of time trying to fix this...I'm guessing I need a name field in qryEngineers.
ContactCombo.jpg
The more pressing matter is:
2) On selecting a Sky Card the [Sky Card Number] on frmContactDetails is displayed in the combo box, exactly as I would like it to be, and the Sky Card ID is stored in the Contacts table. However, when I reload the contact's record in the form the combo box is blank, but the [Sky Card ID] has been stored.
ContactsForm.jpg
I have a similar setup in frmSkyCardDetails where an Engineer is assigned to the card. That one works perfectly and as far as I can tell is set up in exactly the same way.
After quite a few hours of frustration, I have given up. Any advice on what could be going on here would be hugely appreciated.
Thanks in advance,
Jay