Blank Combo Box hair-pulling conundrum.

[JayF]

Hi all,

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:

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; 

The combo box on the form is Bound to SkyCards.[Sky Card ID] (In the above example column 4, but it has been moved around in troubleshooting). I have had the column width for [Sky Card ID] set to 0cm , whichever column it might be (I have tried it with a width with the same result (problem "2)" below). The field Contacts.[Sky Card ID] is also set up as a lookup to qryUnassignedSCards in the table design.

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

[ADezii]

Jay, this is one of those situations, at least for me, that is impossible to diagnose without having the DB directly in front of me. If you are willing to Convert it to Access 2003, remove any Personal Info, then I will be happy to have a look at it.

[JayF]

After some more pondering, I've realised that the combo box is blank as the Limit to List property on the combo box is set to Yes and the value is no longer in the List once it has requeried. Once it has been assigned to a customer it no longer appears in qryUnassignedSCards, which is the Row Source.

However, the Limit to List property can only be set to No if the first Visible column is also the Bound Column (for reasons that aren't clear to me). I don't want to display the bound column as this is [Sky Card ID]. I want to display the [Sky Card Number].

I also only want to display cards that have not yet been assigned to a contact, to prevent the same card being assigned to two different contacts.

I hope I'm being clear enough. My question now is: how can this be done another way?

[JayF]

Thanks ADezii. It's currently only full of Dummy Data. I'll empty most of it out to reduce the size and upload it.

[JayF]

It appears the DB was somehow corrupted. I need redo a few things before I upload a backup.

[JayF]

Attached is a zip containing both the .accdb and .mdb versions.

I think some of the functionality might have been lost in conversion, but hopefully this is good enough for you to see where I'm going wrong. It would take me literally hours to figure out why it's not 100% compatible and how correct it.

Thanks again in advance.

[JayF]

By the way, comments and constructive criticism from anyone who's looked at it are more than welcome. Building this has been a pretty steep learning curve. I'm sure it's probably full of bad practice and inefficiencies, but I think it's pretty functional, for a first attempt.

[ADezii]

@JayF:
I had a chance to take a look at your DB, and the reasons as to why the [Sky Card Number] is not being displayed, but the [Sky Card ID] is being stored, is not that obvious at first glance. I think that it has to do with the interaction between the Record Source for frmContractDetails (Contracts Extended - based on Contracts Table), and especially the Row Source of the Sky Card Combo Box (qryUnassifnedSCards) being based on a LEFT OUTER JOIN on the SkyCards Table with matching Contacts. I download an Attachment that will correctly display the Sky Card Number under all circumstances, notice the slight difference in the Row Source in that it is not joined to Contacts. When I have some time, I'll look into it further, but you will probably have found the solution by then. See Attachment.