Insert Into

  • Thread starter Thread starter Gaetanm via AccessMonster.com
  • Start date Start date
G

Gaetanm via AccessMonster.com

I'm new at this and I have Inherited this data base.
There are issues such as spaces in column names columns miss lable to what
they represent and other challenges.

I'm trying to add records (one at a time) into a job sheet table via a form.

The Job sheet table consist of the following:
Job ID Auto
CustomerID Number
Customer Name Number (this is NOT a
type O it is a #
and not a name)
Customer Order No Text
Start Date Date/Time (short)
Stop Date Date/Time (short)
Contact Text
Invoice No Number
Notes Memo
Markup Number
Supplier ID Number
Sales Tax Number

On my form I have the following:

cboCustomer Customer ID # from Customer table
Text box =cboCustomer.Column (1)
(this shows customer name)
txtDate Date()
txtJobDescrition Text

I'm trying to INSERT through a command button

cboCustomer to Customer Name

txtDate to Start Date

txtJobDescrition to Notes

The following is the SQL I started with cboCustomer and txtDate and got run-
time error 3134 something wrong with me syntax. Can you please help I can't
go to txtJobDescrition till at least this portion works.


Private Sub RECORD_Click()
DoCmd.SetWarnings True
Dim SQLstrg As String

SQLstrg = "INSERT INTO Job Sheet (Customer Name,Start Date,)" & "VALUES(" &
Me.[cboCustomer] & ",#" & Me.[txtDate] & "#)"

DoCmd.RunSQL SQLstrg



End Sub

PS This String is one continuos line

Thanks
Gaetanm
 
While I don't understand why you aren't just using a bound form to do
the data entry. Here is a correction to your SQL statement.

SQLstrg = "INSERT INTO [Job Sheet] ([Customer Name],[Start Date])" &
"VALUES(" & Me.[cboCustomer] & ", #" & Me.[txtDate] & "#)"

If you want to include notes (text) in this then you need something like:

SQLstrg = "INSERT INTO [Job Sheet] ([Customer Name],[Start Date],
Notes)" & "VALUES(" & Me.[cboCustomer] & ", #" & Me.[txtDate] & "# ," &
Chr(34) & me.Txtnotes & Chr(34) & ")"

The Chr(34) add quotes marks around the outside of the notes text. Note
that this SQL will error if txtNotes has any quote marks in it. If that
is the case, you have a couple of choices to fix it. Post a new message
if you should need help on that.


When field or table names have spaces in them the MUST be surrounded
with [] (square brackets). Also the brackets must almost always be used
when the names contain any of non-alpha or non-number character other
than the underscore character.

The good news is that you can always include the brackets if you don't
want to worry about whether they are needed or not needed.
I'm new at this and I have Inherited this data base.
There are issues such as spaces in column names columns miss lable to what
they represent and other challenges.

I'm trying to add records (one at a time) into a job sheet table via a form.

The Job sheet table consist of the following:
Job ID Auto
CustomerID Number
Customer Name Number (this is NOT a
type O it is a #
and not a name)
Customer Order No Text
Start Date Date/Time (short)
Stop Date Date/Time (short)
Contact Text
Invoice No Number
Notes Memo
Markup Number
Supplier ID Number
Sales Tax Number

On my form I have the following:

cboCustomer Customer ID # from Customer table
Text box =cboCustomer.Column (1)
(this shows customer name)
txtDate Date()
txtJobDescrition Text

I'm trying to INSERT through a command button

cboCustomer to Customer Name

txtDate to Start Date

txtJobDescrition to Notes

The following is the SQL I started with cboCustomer and txtDate and got run-
time error 3134 something wrong with me syntax. Can you please help I can't
go to txtJobDescrition till at least this portion works.


Private Sub RECORD_Click()
DoCmd.SetWarnings True
Dim SQLstrg As String

SQLstrg = "INSERT INTO Job Sheet (Customer Name,Start Date,)" & "VALUES(" &
Me.[cboCustomer] & ",#" & Me.[txtDate] & "#)"

DoCmd.RunSQL SQLstrg



End Sub

PS This String is one continuos line

Thanks
Gaetanm
Click to expand...
 
John said:
While I don't understand why you aren't just using a bound form to do
the data entry. Here is a correction to your SQL statement.

SQLstrg = "INSERT INTO [Job Sheet] ([Customer Name],[Start Date])" &
"VALUES(" & Me.[cboCustomer] & ", #" & Me.[txtDate] & "#)"

If you want to include notes (text) in this then you need something like:

SQLstrg = "INSERT INTO [Job Sheet] ([Customer Name],[Start Date],
Notes)" & "VALUES(" & Me.[cboCustomer] & ", #" & Me.[txtDate] & "# ," &
Chr(34) & me.Txtnotes & Chr(34) & ")"

The Chr(34) add quotes marks around the outside of the notes text. Note
that this SQL will error if txtNotes has any quote marks in it. If that
is the case, you have a couple of choices to fix it. Post a new message
if you should need help on that.

When field or table names have spaces in them the MUST be surrounded
with [] (square brackets). Also the brackets must almost always be used
when the names contain any of non-alpha or non-number character other
than the underscore character.

The good news is that you can always include the brackets if you don't
want to worry about whether they are needed or not needed.
I'm new at this and I have Inherited this data base.
There are issues such as spaces in column names columns miss lable to what
Click to expand...
[quoted text clipped - 54 lines]
Thanks
Gaetanm
Click to expand...
Click to expand...
John
Thanks for the reply
I have been away from this for a few days I have taken everthing off my plate
and just focusing on this. I tried your code:


Private Sub RECORD_Click()
DoCmd.SetWarnings True
Dim SQLstrg As String

SQLstrg = "INSERT INTO Job Sheet (Customer Name,Start Date,)" & "VALUES(" &
Me.[cboCustomer] & ",#" & Me.[txtDate] & "#)"

DoCmd.RunSQL SQLstrg



End Sub

I received the following Run- Time Error 3134

Syntax error in INSERT to statement

Any Ideas

Gaetanm
 
Gaetanm said:
While I don't understand why you aren't just using a bound form to do
the data entry. Here is a correction to your SQL statement.
Click to expand...
[quoted text clipped - 26 lines]John
Thanks for the reply
I have been away from this for a few days I have taken everthing off my plate
and just focusing on this. I tried your code:

Private Sub RECORD_Click()
DoCmd.SetWarnings True
Dim SQLstrg As String

SQLstrg = "INSERT INTO Job Sheet (Customer Name,Start Date,)" & "VALUES(" &
Me.[cboCustomer] & ",#" & Me.[txtDate] & "#)"

DoCmd.RunSQL SQLstrg


End Sub

I received the following Run- Time Error 3134

Syntax error in INSERT to statement

Any Ideas

Gaetanm
Click to expand...


I have found the erros of my ways. I re evaluated the statements
and paid closer attetion to the brackets as you suggested and
made dure the fields match and its working like a charm.

Thanks
for your help

Gaetanm
 
Back
Top