Data Entry Form Gone Awry

[drrocket5292]

hi,
I have a form that has several controls bound to a query. I added a new table named address to my database and then I added a couple of textboxes to my form today so that the user could type in the customers address and via a query it would fill into the new address table. the address table is linked to the other tables with a foreign key, so I updated the underlying query that is the recordsource of my form and I added all of the fields of the new address table with a left join statement to it so that I could bound their controls on the form. Now I have all of the controls bounded on the form to the query but now when I open the form, it goes to the first record of the query instead of being blank for data entry like it used to be. I dont know what I did and I dont know how to switch it back to where the controls of the form are blank when i open it so that I can enter data in it and have it go into a new record in the query. I tried putting in code when the form opens that says:

docmd.gotorecord , , acnewrec

but when I put that code in, my form wont open at all. Does anyone know what I did to make it start displaying the first record of the query and does anyone know how I can change that so that it becomes a data entry form again? it's data entry property is already set to yes.

 

[mhcruella]

It sounds like there are 3 or more tables in your underlining query. When this happens, Access likes to make your resulting recordset non-updateable. This means that Access will not let you type in a new record.

Can you post the SQL for your query?

 

[drrocket5292]

youre totally right, when i added the third table is when i started having the problem, how do you fix this? here's my sql statement:

SELECT [msbid].[MSBID], [msbid].[Name], [msbid].[BC], [msbid].[comments], [account].[Account], [account].[NameID], [account1].[account], [account1].[nameid], [account1].[msbcode], [account1].[regcode], [addresses].[addressid], [addresses].[address], [addresses].[city], [addresses].[state], [addresses].[zip], [addresses].[msbid]
FROM (msbid LEFT JOIN (account LEFT JOIN account1 ON [account].[account]=[account1].[account]) ON [msbid].[msbid]=[account].[nameid]) LEFT JOIN addresses ON [msbid].[msbid]=[addresses].[msbid];

 

[mhcruella]

Sometimes it is best to split the query and make a subform. For example, the account name and ID would be on your main form and the address (from the other table) would be in a subform on the main form. By linking the subform to the form, you are able to stil utilize the join you want, but in a way that will still be updateable.

 

[missinglinq]

I'm confused by your post, mbcruella. I assume it's based on your own personal experience, but there must be factors other than "3 or more tables" in the underlying query that leads Access to make a recordset non-updatable. I've got multiple forms based on 3 or more tables that ARE in fact updatable! Access is indeed quirky, and I just wonder what other factors could come into play here!



The Missinglinq

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

 

[mhcruella]

I agree that this doesn't happen just because there are three tables. This is from Access Help - searched for non-updateable:

Data cannot be updated if:
Query based on three or more tables in which there is a many-to-one-to-many relationship.
Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).

Just a note: It is usually not a good thing to set the recordset to Inconsistent Updates as this causes many widows and ophans.

 

[TheAceMan1]

How are ya drrocket5292 . . .

To immediately tell if your [blue]recordset is updateable[/blue], when the form 1st opens, have a look at the [blue]NewRecord[/blue]

navigation button. If it has that disabled look

, recordset is not updateable . . . otherwise your ok and should have a look at the forms [blue]Data Entry[/blue] property.

See Ya! . . . . . .

 

[missinglinq]

That's good to know, mbcruella. Knew it couldn't JUST be three or more tables! And as frequently happens, TheAceMan1 adds his two cents worth, which is always spot on and always worth about a gazillion dollars, which is a pretty good Return on Investment for 2 cents!

You guys have a great Holiday Season!

The Missinglinq

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