Form Edit shows no records- looks like Form Add

[kjyoung]

Hello-

I'm struggling with a form that should be opening in "edit" configuration. However it looks more like it is opening in "add" configuration. I'm using the switchboard and the command is correctly set to 3. I've read quite a few threads on this problem including: thread t=157355
from this forum (sorry, can't post links yet)
and tried almost everything including converting the switchboard macros to VBA to see if command 3 has an acFormAdd where there should be an acForm edit (the macro to VBA conversion made the switchboard die).
I have an earlier iteration of the db that does correctly open the form in edit mode and populate it with all records and I haven't seen any differences in the form properties.
Using Access 2007 and I'm not a sophisticated Access programmer.

Thanks for any advice!

 

[gemma-the-husky]

option 3 in the menu is DEFINITELY browse mode - ie shows all existing records. [note - A2003 usage]

I would check your form independently of the menu, and see if it opens correctly.

-----
However, is this using a A2003 menu, converted to A2007, or a native A2007 menu. If the latter, then I* suppose it MAY work differently. In which case, I would check the code and see what option 3 does, in the handlebuttonclick event.

I actually use a modfiied A2003 menu in all my apps, and have never tried the A2007 menu directly

 

[kjyoung]

Thanks. When I open the form from the quick access tool bar rather than from the switchboard it does the same thing- opens like it's in Add mode not Edit mode. (it should open with all the records loaded when the from is started from the quick access toolbar but it doesn't)

I'm not sure where to check the handlebuttonClick event??

thank you.

 

[RuralGuy]

Look on the Data tab of the Properties sheet for the form and see if DataEntry is set to YES. It should be NO.

 

[kjyoung]

Hi-

The data entry is set to No.

Three things: 1) I am using the same form for both adding and editing (the form is called by command 2 (for adding) and command 3 (for editing) using the switchboard. 2) Even when I open it from the quick access toolbar the thing opens in Add mode (ie. not populated with existing records (my experience is that forms opened from the quick access toolbar normally always open in Edit [browse] mode, and 3) in creating the form I scabbed a subform to it to input data into an associative table.

The form is working fine for inputting records but could the form/subform relationiship impact the Edit mode somehow?

Thanks

 

[RuralGuy]

If you could zip up your db and attach it to a post, maybe we could see something. If the system won't let you attach a file yet then maybe one of the free file hosting sites.

 

[kjyoung]

OK. I think that would be great. I'll zip and post it.

 

[kjyoung]

View attachment EES_QA_JobsV5_Backup.accdb

View attachment EES_QA_JobsV5_Backup.zip

I am hoping the database is attached. ?????

 

[RuralGuy]

The RecordSource for the [frm_QA Job Input Form] form returns *no* records so it would make no difference if you were adding a new record or editing old ones.

 

[missinglinq]

A recordset with no records wouldn't show records, would it?

 

[kjyoung]

I see this line under Recordsource for the form:

SELECT [tbl_QA Job].*, tbl_Program.Program_Name, tbl_ContractingCompany.Company_Name, tbl_Zone.Zone AS Zone_tbl_Zone, tbl_Major.[Error Description], [tbl_QA Specialist].L_Name_QA AS [L_Name_QA_tbl_QA Specialist], [tbl-Minor].[Error Description] AS [Error Description_tbl-Minor], tbl_Customer.[Customer_Name_L or Business Name], tbl_Customer.Customer_Name_F, tbl_Customer.[Acct No],

It looks like there are a bunch of tables and fields. The controls in the form aren't orphaned? Why is it not returning records?

I built the form with the wizard and I don't have a good handle on the recordsource. Can I rebulid the recordsource?

Thanks,

 

[kjyoung]

Actually the RecordSource looks more like this:

SELECT [tbl_QA Job].*, tbl_Program.Program_Name, tbl_ContractingCompany.Company_Name, tbl_Zone.Zone AS Zone_tbl_Zone, tbl_Major.[Error Description], [tbl_QA Specialist].L_Name_QA AS [L_Name_QA_tbl_QA Specialist], [tbl-Minor].[Error Description] AS [Error Description_tbl-Minor], tbl_Customer.[Customer_Name_L or Business Name], tbl_Customer.Customer_Name_F, tbl_Customer.[Acct No], tbl_Customer.[Zip Code], tbl_Customer.Address, tbl_Customer.City, tbl_Customer.Phone1, tbl_Customer.Phone2, [tbl_QA Job-Minor].Minor_ID, [tbl_QA Job-Minor].[QA Job_ID] AS [QA Job_ID_tbl_QA Job-Minor], [tbl-Minor].Minor_ID AS [Minor_ID_tbl-Minor]
FROM tbl_Zone INNER JOIN ([tbl_QA Specialist] INNER JOIN (tbl_Program INNER JOIN ((tbl_Major INNER JOIN (tbl_Customer INNER JOIN (tbl_ContractingCompany INNER JOIN ([tbl_QA Job] INNER JOIN [tbl-Minor] ON [tbl_QA Job].[QA Job_ID] = [tbl-Minor].Minor_ID) ON tbl_ContractingCompany.ContractingCompany_ID = [tbl_QA Job].ContractingCompany_ID) ON tbl_Customer.Customer_ID = [tbl_QA Job].Customer_ID) ON tbl_Major.Major_ID = [tbl_QA Job].Major_ID) INNER JOIN [tbl_QA Job-Minor] ON [tbl-Minor].Minor_ID = [tbl_QA Job-Minor].Minor_ID) ON tbl_Program.Program_ID = [tbl_QA Job].Program_ID) ON [tbl_QA Specialist].L_Name_QA = [tbl_QA Job].L_Name_QA) ON tbl_Zone.Zone = [tbl_QA Job].Zone;

It looks like it should return lots of records, but I'm not a SQL programmer.

???

 

[kjyoung]

OK. Resolved it in a not so elegant way. Created the exact same form with the proper fields included (using Wizard again) and then replaced the SQL code in the old broken form with the RecordSource SQL code from the new form. The broken form is fixed.

Thanks tons for the help.

 

[RuralGuy]

Excellent! Thanks for posting back with your success.

 

[dgodin]

I had(have) this same problem with a form. But I noticed on my ribbon bar (which I did not have minimized) that in the Sort & Filter area that the Toggle Filter button was enabled. I clicked on it and all the records showed up. For the time being, I have told users to click on the "filtered/unfiltered" selector in the records navigation portion of the form. This also toggles a filter off and on. I don't have this problem in another database, which also uses an 03 switchboard (I am using 07). I did note that I could affect a more permanent fix in the Ribbon Sort & filter, by selecting Advanced and selecting Clear all Filters. My thinking was that a filter, once applied is "sticky" and is saved in some variable. I was looking for a way to clear all filters when the form is opened in edit mode but haven't figured it out.

Perhaps I'd be better off just to re-create the form.

 

[boblarson]

In Access 2007 and above there is a property on the form that is called FILTER ON LOAD and you should set it to NO.