UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Run-time error 2105    
 
   
itauditor
post Oct 18 2010, 04:30 PM
Post#1



Posts: 22
Joined: 6-October 10



Simply trying to insert a new record in form using a button:
nly one table affected
DoCmd.GoToRecord , , acNewRec
For some reason, the form gives this error. Seems basic... any ideas for a newby?
Thanks In Advance
Go to the top of the page
 
Doug Steele
post Oct 18 2010, 05:59 PM
Post#2


UtterAccess VIP
Posts: 21,969
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Is the form based on a query or a table? If a query, is the query updatable? Regardless of which, does the form have its AllowAdditions property set to False?
Go to the top of the page
 
itauditor
post Oct 19 2010, 08:34 AM
Post#3



Posts: 22
Joined: 6-October 10



It is based upon a query which is for a single table. Allow additions is set to 'Yes'.
Go to the top of the page
 
Doug Steele
post Oct 19 2010, 08:40 AM
Post#4


UtterAccess VIP
Posts: 21,969
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Is the query updatable? If you open it by itself, can you add to it?
Go to the top of the page
 
itauditor
post Oct 19 2010, 09:23 AM
Post#5



Posts: 22
Joined: 6-October 10



for some reason I can't insert records into it:
SELECT DISTINCT Contestants.FirstName, Contestants.LastName, Contestants.EmailAddress, Contestants.Club, Contestants.IPMSNumber, Contestants.Division, Contestants.ConID, Contestants.EntryCount, Contestants.EntryFee
FROM Contestants;
perhaps I should just not even use the query and use the table the record source. I'm going to try that. Any other ideas?
Go to the top of the page
 
itauditor
post Oct 19 2010, 09:31 AM
Post#6



Posts: 22
Joined: 6-October 10



the insert works now, but it is a mystery why the query wouldn't allow the addition...
Go to the top of the page
 
JanR
post Oct 19 2010, 09:35 AM
Post#7



Posts: 29
Joined: 6-July 10
From: Norway


SELECT DISTINCT .........
Makes it unupdateble, remove the Destinct key word and it should be updateble.
JR
Go to the top of the page
 
itauditor
post Oct 19 2010, 10:03 AM
Post#8



Posts: 22
Joined: 6-October 10



Removing "DISTINCT" from my query has created another problem. I also tried to use the table alone as the recordsource.
I am passing a key value to the form to select a record to update. I am using the Filter to do a DLookup function when the form is loaded. Now the form only allows creates a new record when the form is loaded.
sorry this is baffling me...
TIA
Go to the top of the page
 
Doug Steele
post Oct 19 2010, 10:19 AM
Post#9


UtterAccess VIP
Posts: 21,969
Joined: 8-January 07
From: St. Catharines, ON (Canada)


There's no way to make it work with the DISTINCT keyword in the query.
I'm not sure what you mean by "the form only allows creates a new record when the form is loaded". Are you saying you can create a new record once when the form is first loaded, but after that you can't add new records?
Go to the top of the page
 
itauditor
post Oct 19 2010, 10:25 AM
Post#10



Posts: 22
Joined: 6-October 10



sorry for my bad spelling.
When the form loads it defaults to a new record instead of using the filter to find a specific record from the DLookup. I am storing the filter key value in a Control table then doing a DLookup to find the value for the key of the record that I want to edit. Removing DISTINCT from the recordsource query does not allow the filter to operate properly. Also selecting the table directly as a recordsource does not allow the filter to work to select the chosen record.
Hope this does a bettter job of explaining.
Go to the top of the page
 
Doug Steele
post Oct 19 2010, 10:31 AM
Post#11


UtterAccess VIP
Posts: 21,969
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Using the table as the recordsource would be identical to using the query without the DISTINCT keyword.
That's the code you're currently using? (not just the one line you've shown so far...)
Go to the top of the page
 
itauditor
post Oct 19 2010, 11:42 AM
Post#12



Posts: 22
Joined: 6-October 10



Data Properties of my form
ecordSource = Contestants
Recordset Type = Dynaset
Fetch Defaults = Yes
Filter = ConID=DLookup("HoldConID","Control","[ID]=1")
Filter On Load = Yes
Order By On Load = No
Data Entry = Yes
Allow Additions = Yes
Allow Deletions = Yes
Allow Edits = Yes
Allow Filters = Yes
Record Locks = No Locks
HoldConId value is stored in the Control table from the previous form.
ConID is the key value for the Contestants table
Omanually maintain this key (no autonumber) by using this function as the Default Value on the Text Box Data tab:
=DMax("ConID","Contestants")+1
I also tried using a query to select the Record Source - that is when this DISTINCT issue on the SQL parm started
Go to the top of the page
 
Doug Steele
post Oct 19 2010, 11:47 AM
Post#13


UtterAccess VIP
Posts: 21,969
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You still haven't really answered my question about what you mean by "Now the form only allows creates a new record when the form is loaded.". How is what the form's doing different from what you want it to do?
lso, with what event is the code DoCmd.GoToRecord , , acNewRec associated?
Go to the top of the page
 
itauditor
post Oct 19 2010, 11:56 AM
Post#14



Posts: 22
Joined: 6-October 10



Sorry - wasn't clear
The form automatically loads with a new record ready for entry, not the filtered record.
The new record DoCmd is associated with a button click.
Otried duplicating the form into one for updating existing records and another for adding new records. This allows the record sources to be different.
The problem in my previous post was from the "Update" form. I think I have the Add New form working okay.
BTW thanks for all the support on this
Go to the top of the page
 
Doug Steele
post Oct 19 2010, 12:09 PM
Post#15


UtterAccess VIP
Posts: 21,969
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I don't really understand why you've got the filter set like that.
Why not just provide the filter when you open the form?
CODE
  DoCmd.OpenForm "MyForm", acNormal, , "ConID=" & DLookup("HoldConID","Control","[ID]=1")
Go to the top of the page
 
itauditor
post Oct 19 2010, 12:23 PM
Post#16



Posts: 22
Joined: 6-October 10



Doing that doesn't change anything. The form still opens defaulting to a new record instead of the DLookup record passing in the openform statement.
doublechecked the Control table where the ConID is stored - the value is there... and a valid ConID
Otried it with both a query recordsource and a straight table recordsource.
Perhaps my form is somehow corrupted behind the scenes. I have had that happen before - creating a new form then copying all the VBA and stuff over has fixed it...
Go to the top of the page
 
Doug Steele
post Oct 19 2010, 12:27 PM
Post#17


UtterAccess VIP
Posts: 21,969
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Yeah, you may want to try creating a new form to test it. If the value returned by the DLookup does in fact exist in the table then something is wonky with the existing form.
Go to the top of the page
 
itauditor
post Oct 19 2010, 12:37 PM
Post#18



Posts: 22
Joined: 6-October 10



That fixed it. Have to pretty up the form now and cc the VBA.
hould have known - it didn't make any sense...
thanks for all the support and ideas!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd October 2018 - 05:44 PM