Combobox doesn't select anything

[jazminecat]

Hi, I have a mission critical database that i inherited that has acted strange lately. I have a form based on the following code:

SELECT Rights.Department, Tbl_ASR_Main.ASR, Tbl_ASR_Main.UnitID, Rights.Division, Tbl_ASR_Main.Fund, Tbl_ASR_Main.Attachment, Tbl_ASR_Main.CostCntr, Tbl_ASR_Main.Originator, Tbl_ASR_Main.Name_Request, Tbl_ASR_Main.Data_Entry_Date, Tbl_ASR_Main.Descrip_Requested, Tbl_ASR_Main.Total_Amt_Approved, Tbl_ASR_Main.Objectives, Tbl_ASR_Main.Ongoing, Tbl_ASR_Main.Personnel, Tbl_ASR_Main.Review_Date, Tbl_ASR_Main.Exec_Approv_Status, Tbl_ASR_Main.Council_Approv_Status, Tbl_ASR_Main.Data_Entry_Notes, Tbl_ASR_Main.Change_Code, Tbl_ASR_Main.Priority, Tbl_ASR_Main.Budget_Adm_Comments, Tbl_ASR_Main.ASRType, Tbl_ASR_Main.Problem, Tbl_ASR_Main.Customer, Tbl_ASR_Main.Need, Tbl_ASR_Main.CustService, Tbl_ASR_Main.Advantages, Tbl_ASR_Main.Outcomes, Tbl_ASR_Main.OutcomeDate, Tbl_ASR_Main.Measure, Tbl_ASR_Main.Impacts, Tbl_ASR_Main.WhoResponsible, Tbl_ASR_Main.WhoMeasures, Tbl_ASR_Main.OtherKeyPlayers, Tbl_ASR_Main.PotentialFunding, Tbl_ASR_Main.Flag_Space, Tbl_ASR_Main.Addl_FTE
FROM Rights INNER JOIN Tbl_ASR_Main ON Rights.[Unit ID] = Tbl_ASR_Main.UnitID
ORDER BY Rights.Department, Tbl_ASR_Main.ASR, Rights.Division, Tbl_ASR_Main.Priority
WITH OWNERACCESS OPTION;

as of last thursday, this form was working fine. I have a combobox who's control source is UnitID, from which the user selects their unit. The row source is the following query:

SELECT Rights.[Unit ID], Rights.Department, Rights.Division
FROM Rights
ORDER BY Rights.Department, Rights.Division
WITH OWNERACCESS OPTION;

This is part of a distributed database, and the rights table is populated when they login giving them access only to the items that effect their department. However, Now, if they try to select anything, it just beeps. It doesn't allow any selection at all.

Any thoughts? None of this code has changed from Thursday. however, on Friday we had a corrupt record, and had to rebuild a table from a backup copy. We have doublechecked the code, and I just don't know where to look at this point so any ideas at all would be most appreciated.

Thanks!

 

[kjv1611]

Have you verified that when you went to the backup copy of the table, that the table names and field names matched the references in your code? Any slight changes would have to be matched up between the two (like if the table got called "MyTable1" instead of "MyTable").

 

[jazminecat]

Yes, the query itself works. When I run it, it does return all the expected records.

 

[kjv1611]

How are you running the code?

What is the full VBA code that is running the SQL above, or the macro?

 

[missinglinq]

I had a similar problem in the past when I deleted a table that a combobox was based on, then imported the backup copy of the table; the combobox would no longer function. I finally deleted the combobox and recreated it and it worked fine. It seems that sometimes Access doesn't recognize the replacement table even though it has the identical name and structure.

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[jazminecat]

Missinglinq - your suggestion got me thinking, so i redeployed my front end, after the whole fixing of the backend table last week.

Recreating the mde and redeploying seems to have fixed my problem. I still don't know *why* this happened, which any other day would make me crazy, but since I'm so busy right now anyway, I'm going with the alchemy and just being grateful that seemed to work. thanks for your input, both of you.

 

[missinglinq]

Glad you got it licked!

The Missinglinq

There's ALWAYS more than one way to skin a cat!