Compile error: variable not defined

PuddinPie

Registered User.
Local time
Today, 02:13
Joined
Sep 15, 2010
Messages
149
Hello again.

This is probably going to be and easy one for you all but I just cant figure it out. I'm trying to get the inputed data from a forms test boxes to be put into a new row in a table.
Here's what I've got:

Private Sub AddNewUser_Click()

SQL = "INSERT INTO tblUserInfo(FirstName, LastName, Address, City, Province, PostalCode, Country, EmalAddress, HomePhone, MobilePhone, FaxNumber) VALUES (FORMS!UserInfo!FirstName, FORMS!UserInfo!LastName, FORMS!UserInfo!Address, FORMS!UserInfo!City, FORMS!UserInfo!Province, FORMS!UserInfo!PostalCode, FORMS!UserInfo!Country, FORMS!UserInfo!EmailAddress, FORMS!UserInfo!HomePhone, FORMS!UserInfo!MobilePhone, FORMS!UserInfo!FaxNumber)"

DoCmd.RunSQL SQL

End Sub

I want it to happend on click but I get the Compile error every time.
Any help would be nice.
Thank you.
 
Hello again.

This is probably going to be and easy one for you all but I just cant figure it out. I'm trying to get the inputed data from a forms test boxes to be put into a new row in a table.
Here's what I've got:

Private Sub AddNewUser_Click()

SQL = "INSERT INTO tblUserInfo(FirstName, LastName, Address, City, Province, PostalCode, Country, EmalAddress, HomePhone, MobilePhone, FaxNumber) VALUES (FORMS!UserInfo!FirstName, FORMS!UserInfo!LastName, FORMS!UserInfo!Address, FORMS!UserInfo!City, FORMS!UserInfo!Province, FORMS!UserInfo!PostalCode, FORMS!UserInfo!Country, FORMS!UserInfo!EmailAddress, FORMS!UserInfo!HomePhone, FORMS!UserInfo!MobilePhone, FORMS!UserInfo!FaxNumber)"

DoCmd.RunSQL SQL

End Sub

I want it to happend on click but I get the Compile error every time.
Any help would be nice.
Thank you.

Look at using something like this:

Dim db As DAO.Database
'Use the table = "tblFormUpdated"
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblFormUpdated")
'Add a new record completing the FormUpdate field (this is a textbox with a date/time format)
'Add the user name to the ByUser Field
'Finally update the record
With rst
.AddNew
.Fields("FormUpdated").Value = Date
.Fields("GlassCleaner").Value = txtGlassCleaner.Value

.Update
End With

It uses a form elements and then adds a new record once clicked. You would have to adjust the table name and field names, also have you thought of an append query?
 
Holy crud. That actualy worked. Thank you very much.
 
Last edited:
Also, just an FYI - The reason you were having problems is that the form references really need to be OUTSIDE of the quotes, so they provide the values.
Code:
SQL = "INSERT INTO tblUserInfo(FirstName, LastName, Address, City, Province, PostalCode, Country, EmalAddress, HomePhone, MobilePhone, FaxNumber) VALUES ([B][COLOR=red]'" &[/COLOR][/B] FORMS!UserInfo!FirstName [COLOR=red][B]& "','" &[/B][/COLOR] FORMS!UserInfo!LastName[COLOR=red][B] & "','" &[/B][/COLOR] FORMS!UserInfo!Address [COLOR=red][B]& "','" &[/B][/COLOR] FORMS!UserInfo!City [COLOR=red][B]& "','" &[/B][/COLOR] FORMS!UserInfo!Province...etc.
 
Ok. What about this then. When the data is entered it creates a new row with a random ID#. How do I get the ID number to populate in the form after the update?
 
Ok. What about this then. When the data is entered it creates a new row with a random ID#. How do I get the ID number to populate in the form after the update?

My question is why aren't you just using a bound form?
 
I dont what to bind the whole form because when I do it opens with table data already in it and I don't want that. I just want the user to input data. Click on Add and have the form refresh with the data they just entered and the new autonumber that is assigned to that row populate in the correct field.
 
I dont what to bind the whole form because when I do it opens with table data already in it and I don't want that. I just want the user to input data. Click on Add and have the form refresh with the data they just entered and the new autonumber that is assigned to that row populate in the correct field.

A bound form can be opened to just let them enter data (with no data showing). You set the DATA ENTRY property to YES.
 
How do you create a bound form or bind my existing form?
 
You select the table or query (be careful if you use a query to not try to include extra tables you don't need in it) from the form's RECORD SOURCE property on the DATA tab (properties dialog).

You then can go to each control and bind it to the appropriate field by selecting the field from the drop down under CONTROL SOURCE.
 
Ok. That works. One more question (for now). Is there a way of telling the auto number field, which is my primary key, not to be a negative number. To only use positive numbers? If not is there a way of incremental numbers to start for a certian number and not 1?
 
If you are using Autonumbers and you CARE what it is, then you are using it for the wrong reasons. It is guaranteed to give you a UNIQUE number but not in any specific order or not. Most of the time people see an autonumber and its property is set in the table to be incremental and people think that it will always work that way. Well it doesn't necessarily.

It is meant to be used as a surrogate key (primary key) in either the absence of, or as a substitute for a natural primary key. These should really only be used by the system to maintain referential integrity and you and your users should not care what the key is when using them.

If you care, then you'll have to venture into the DMax+1 territory but that has drawbacks as well.
 

Users who are viewing this thread

Back
Top Bottom