Insert Into to Append Record to Table

[sans holo]

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.

Private Sub Command72_Click()
 
Dim strSQL As String
 
   strSQL = "INSERT INTO WLDB (Acct#) VALUES(" & WLAQ!Acct# & ");"
   DoCmd.RunSQL strSQL
 
 
End Sub 

[twinnyfo]

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.

[sans holo]

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:

Private Sub Command72_Click()
 
Dim strSQL As Text
 
 
   strSQL = "INSERT INTO WLDB(AcctNo)VALUES('" & Me.txtAcctNo & "')"
 
   DoCmd.RunSQL strSQL
 
 
 
End Sub
 

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.

[twinnyfo]

If this is a direct copy and paste of your code, then your line 6 should be:

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!

[sans holo]

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?

[sans holo]

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

Private Sub Command72_Click()
Dim db As Database
Dim rec As Recordset
 
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from WLDB")
 
rec.AddNew
rec("AcctNo") = Me.txtAcctNo
rec.Update
 
Set rec = Nothing
Set db = Nothing
 
End Sub

[NeoPa]

In your post #3 you have line #3 as :

Dim strSQL As Text

It should read (as previously) :

Dim strSQL As String