422,929 Members | 1,423 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,929 IT Pros & Developers. It's quick & easy.

Insert Into to Append Record to Table

P: 6
I am attempting to create VBA to run as an event for a command button. I have a Form named WLAQ and I am trying to load the values to the field provided below as a test. I have more values and fields to come but wanted to keep it as simple as possible first time through. I amended the code below which I found online which seemed to work for the person posting the initial question/problem.

I am running into a 424 object required error.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command72_Click()
  2.  
  3. Dim strSQL As String
  4.  
  5.    strSQL = "INSERT INTO WLDB (Acct#) VALUES(" & WLAQ!Acct# & ");"
  6.    DoCmd.RunSQL strSQL
  7.  
  8.  
  9. End Sub 
Apr 13 '18 #1
Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 100+
P: 2,436
sans holo,

Welcome to Bytes!

First, based upon what you have provided, I can only assume that you have a field named "Acct#", which I would advise you to immediately change the name of that field to something without a special character (such as "AcctNo"). SQL does not like special characters introduced when they are not signifying something specific (the "#" character signifies dates).

Second, you are referencing the value on your Form incorrectly. The proper syntax would be Me.Acct#. However, again, you must change the name.

Third, it is unclear from your code, whether you are trying to reference the value from the text box on the form (it could be an unbound text box), or the underlying field. MS Access can do both. However, in many cases, people simply drag and drop the fields onto their forms and the "Name" of the text box become the Name of the Field. In such cases, then, the term Me.Acct# means two different things: 1) the text box sitting on the form with the name "Acct#" and 2) the field underlying that form. In such cases, it can cause some ambiguity in your database, and ultimately, can cause problems. This is why I always stress to Access users that they absolutely must use a standardized naming convention for DB objects (thus, your text box would have the name of txtAcct# -- again, a name change to remove the special character is needed). You can do an internet search for DB naming conventions--any which one you choose will work, just pick one and stick with it.

Hope this heppps.
Apr 13 '18 #2

P: 6
Excellent advice. Lots to learn.

I made the name changes to the Form text box and to the table field that were recommended.

Also made changes to code to match:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command72_Click()
  2.  
  3. Dim strSQL As Text
  4.  
  5.  
  6.    strSQL = "INSERT INTO WLDB(AcctNo)VALUES('" & Me.txtAcctNo & "')"
  7.  
  8.    DoCmd.RunSQL strSQL
  9.  
  10.  
  11.  
  12. End Sub
  13.  
No longer getting a runtime error but am now getting an error that says "User-defined type not defined" There's more but I think that is the gist of the error.
Apr 13 '18 #3

twinnyfo
Expert Mod 100+
P: 2,436
If this is a direct copy and paste of your code, then your line 6 should be:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO WLDB ( AcctNo ) VALUES ( '" & Me.txtAcctNo & "' )"
Notice the spacing.

Additionally, is your account number a text string or is it numeric? If it is numeric, there is no need for the single quotes.

See if that hepps!
Apr 13 '18 #4

P: 6
account number is text. Copied and pasted with code you provided with correct spacing however getting same result.

Possibly something wrong with my linkage to my command button?
Apr 13 '18 #5

P: 6
Found this instead which works. Thanks for help, not sure what the deal was but am certain it was something on my side.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command72_Click()
  2. Dim db As Database
  3. Dim rec As Recordset
  4.  
  5. Set db = CurrentDb
  6. Set rec = db.OpenRecordset("Select * from WLDB")
  7.  
  8. rec.AddNew
  9. rec("AcctNo") = Me.txtAcctNo
  10. rec.Update
  11.  
  12. Set rec = Nothing
  13. Set db = Nothing
  14.  
  15. End Sub
Apr 13 '18 #6

NeoPa
Expert Mod 15k+
P: 30,856
In your post #3 you have line #3 as :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As Text
It should read (as previously) :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
Apr 14 '18 #7

Post your reply

Sign in to post your reply or Sign up for a free account.