Unbound combobox doesn't pull in ALL records

[TeamTraveler]

Can someone please suggest some reasons why an unbound combobox used to pull records from a table into a form will pull some of the records in but not ALL of them.



The combobox has an After Update embedded macro with the following:

Record - First
Where Condition = = "[HotelName] = " & "'" & [Screen].[ActiveControl] & "'"

Thank you in advance.

[June7]

Post the SQL statement of the form RecordSource. Does it have multiple tables with INNER JOIN?

[TeamTraveler]

Post the SQL statement of the form RecordSource. Does it have multiple tables with INNER JOIN?

This is a Hotel Input form tied to a Hotel Master table.

SELECT Hotels.HotelID, Hotels.HotelName, Hotels.DNU, Hotels.Address1, Hotels.Address2, Hotels.City, Hotels.State, Hotels.Zip, Hotels.Contact1Name, Hotels.Contact1Title, Hotels.ContactPhone1, Hotels.Contact1Email, Hotels.Contact2Name, Hotels.Contact2Title, Hotels.ContactPhone2, Hotels.Contact2Email, Hotels.HotelMgmtGroup, Hotels.TACityRank, Hotels.[TA%], Hotels.[TA#Reviews], Hotels.PayComm, Hotels.[Comm%], Hotels.[InteriorRooms?], Hotels.[DblQueens?], Hotels.[BusinessSvcs?], Hotels.[ExerciseArea?], Hotels.[FreeParking?], Hotels.[Guest Laundry?], Hotels.BfastType, Hotels.NearbyEating, Hotels.WillingnessToUse, Hotels.[FreeMeetRooms?], Hotels.ApplicableTaxRate, Hotels.GeneralNotes FROM Hotels;

All of the information is self contained on this one form. The only item I have which doesn't appear affected by things is a subform used to track distance from the hotel to the venue.

As far as inner joins are concerned I don't have any in this individual form. However, if referential integrity was enforced on the overall relationship level could this be possibly be preventing some records from pulling while allowing others in?

[June7]

Simple SELECT query. All records matching filter criteria should be retrieved. Referential integrity should not have impact.

If you want to provide db for analysis, follow instructions at bottom of my post.

Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

The table structure is not truly normalized - as indicated by the multiple yes/no fields but don't think that is part of this issue.

[TeamTraveler]

June,

Thank you for reviewing. I have attached my file.

Matt

[June7]

Relationships getting 'spider-webby', be careful - might review http://www.codeproject.com/Articles/...atabase-Design


[Hotel Dist to Venue] should not have both ID and descriptive fields for hotels and venues - just one or the other (recommend IDs). Then do linking and searching on IDs.


Not seeing the described issue. Hotels has 261 records. All 261 records retrieved by the form. The combobox does 'go to' selected hotel.


Suggest you apply ORDER BY clause in the combobox RowSource sql.

[Missinglinq]

Can you give us an example of what selection doesn't retrieve all related records?

Linq ;0)>

[TeamTraveler]

Can you give us an example of what selection doesn't retrieve all related records?

Linq ;0)>

June, thanks for the time in review and your thoughts. Yes, you are correct - all records in table pull into the form.

However, if you go to the Hotel Input form, and open the combo box, when the top three selections are made, they DO pull the record into the form. The next three records in a row for Birmingham - DON'T pull in. Both Calera hotels pull in. The first two Hoover hotels do NOT, but the next three do. This is what I'm seeing.

When I look at the records to possibly identify what some records have that others don't, I couldn't identify the issue.

[June7]

Okay, that is good to know. Apparently I did not pick one of the problem records. Cause of issue is the apostrophe in the hotel names. Apostrophes and quote marks within SQL are an issue because of their special purposes. The search criteria in the SearchForRecord method is like the WHERE clause of an SQL statement and follows the same rules. Apostrophe and quote marks must be doubled within the string so as to be recognized as text and not special character (this is referred to as 'escaping' special characters). I don't use macros so I converted to VBA.

DoCmd.SearchForRecord , "", acFirst, "[HotelName]='" & Replace(Screen.ActiveControl, "'", "''") & "'"

There would not be an issue if you did search on HotelID. If you don't save the HotelID as foreign key then it serves no purpose to designate it as primary key. Since you save HotelName as foreign key, it should be designated primary key.

[TeamTraveler]

Okay, that is good to know. Apparently I did not pick one of the problem records. Cause of issue is the apostrophe in the hotel names. Apostrophes and quote marks within SQL are an issue because of their special purposes. The search criteria in the SearchForRecord method is like the WHERE clause of an SQL statement and follows the same rules. Apostrophe and quote marks must be doubled within the string so as to be recognized as text and not special character (this is referred to as 'escaping' special characters). I don't use macros so I converted to VBA.

DoCmd.SearchForRecord , "", acFirst, "[HotelName]='" & Replace(Screen.ActiveControl, "'", "''") & "'"

There would not be an issue if you did search on HotelID. If you don't save the HotelID as foreign key then it serves no purpose to designate it as primary key. Since you save HotelName as foreign key, it should be designated primary key.

This worked like a charm! Thank you very much, June.