insert into table 2 fields instead of one

D

deb

How do i alter the below code to insert into the table tSite 2 fields (Site
and CustomerID)
I want to use the Customer ID in the current form(f000Score.CustomerID)

The below code only inserts the field - Site and since CustomerID field is a
required field, it get an error that CustomerID is needed


Private Sub cboSite_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
Dim FindCriteria As String
x = MsgBox("Do you want to add this Site to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tSite ([Site]) values ('" & NewData & "')"
' strsql = "Insert Into tSite ([CustomerID]) values ('" &
[CustomerID] & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
FindCriteria = Me!cboSite.Text
DoCmd.OpenForm "fSiteNotInList", , , , , , FindCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


thanks
 
J

Jeff Boyce

If you take a look at Access HELP for "insert", I believe you'll find the
correct syntax.

Generally (i.e., untested), you'd use something like:

"INSERT into tSite.Site, tSite.CustomerID Values ('" & NewData & "', " &
CustID & ")"

where you passed both NewData and CustID into the function.

Now, why? What are you trying to accomplish (other than inserting two
values into a table, which is more a "how" you are doing something, rather
that why/what you want to accomplish)?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

deb

I am using the "no in list" to add the data from a form the fields that are
not in a drow down combo box.

The table has the fields Site and CustomerID that are both required along
with other fields that are not required.

I want the required fields added and the fSiteNotInList form to open so user
can add other fields for the tSite table. When they close the fSiteNotInList
form the data is in the f000Score form. User will not have to leave
f000Score form to add missing dropdown options. The code I entered will only
add the one field. I am still learning so I looked at the help and am not
sure what to do. Please help

--
deb


Jeff Boyce said:
If you take a look at Access HELP for "insert", I believe you'll find the
correct syntax.

Generally (i.e., untested), you'd use something like:

"INSERT into tSite.Site, tSite.CustomerID Values ('" & NewData & "', " &
CustID & ")"

where you passed both NewData and CustID into the function.

Now, why? What are you trying to accomplish (other than inserting two
values into a table, which is more a "how" you are doing something, rather
that why/what you want to accomplish)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


deb said:
How do i alter the below code to insert into the table tSite 2 fields
(Site
and CustomerID)
I want to use the Customer ID in the current form(f000Score.CustomerID)

The below code only inserts the field - Site and since CustomerID field is
a
required field, it get an error that CustomerID is needed


Private Sub cboSite_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
Dim FindCriteria As String
x = MsgBox("Do you want to add this Site to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tSite ([Site]) values ('" & NewData & "')"
' strsql = "Insert Into tSite ([CustomerID]) values ('" &
[CustomerID] & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
FindCriteria = Me!cboSite.Text
DoCmd.OpenForm "fSiteNotInList", , , , , , FindCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


thanks
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I believe that shoud be

"INSERT into tSite (Site, CustomerID) Values ('" & NewData & "', " & CustID
& ")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Boyce said:
If you take a look at Access HELP for "insert", I believe you'll find the
correct syntax.

Generally (i.e., untested), you'd use something like:

"INSERT into tSite.Site, tSite.CustomerID Values ('" & NewData & "', " &
CustID & ")"

where you passed both NewData and CustID into the function.

Now, why? What are you trying to accomplish (other than inserting two
values into a table, which is more a "how" you are doing something, rather
that why/what you want to accomplish)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


deb said:
How do i alter the below code to insert into the table tSite 2 fields
(Site
and CustomerID)
I want to use the Customer ID in the current form(f000Score.CustomerID)

The below code only inserts the field - Site and since CustomerID field
is a
required field, it get an error that CustomerID is needed


Private Sub cboSite_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
Dim FindCriteria As String
x = MsgBox("Do you want to add this Site to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tSite ([Site]) values ('" & NewData & "')"
' strsql = "Insert Into tSite ([CustomerID]) values ('" &
[CustomerID] & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
FindCriteria = Me!cboSite.Text
DoCmd.OpenForm "fSiteNotInList", , , , , , FindCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


thanks
Click to expand...
Click to expand...
 
D

deb

Thank you!!!
--
deb


Douglas J. Steele said:
I believe that shoud be

"INSERT into tSite (Site, CustomerID) Values ('" & NewData & "', " & CustID
& ")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Boyce said:
If you take a look at Access HELP for "insert", I believe you'll find the
correct syntax.

Generally (i.e., untested), you'd use something like:

"INSERT into tSite.Site, tSite.CustomerID Values ('" & NewData & "', " &
CustID & ")"

where you passed both NewData and CustID into the function.

Now, why? What are you trying to accomplish (other than inserting two
values into a table, which is more a "how" you are doing something, rather
that why/what you want to accomplish)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


deb said:
How do i alter the below code to insert into the table tSite 2 fields
(Site
and CustomerID)
I want to use the Customer ID in the current form(f000Score.CustomerID)

The below code only inserts the field - Site and since CustomerID field
is a
required field, it get an error that CustomerID is needed


Private Sub cboSite_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
Dim FindCriteria As String
x = MsgBox("Do you want to add this Site to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tSite ([Site]) values ('" & NewData & "')"
' strsql = "Insert Into tSite ([CustomerID]) values ('" &
[CustomerID] & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
FindCriteria = Me!cboSite.Text
DoCmd.OpenForm "fSiteNotInList", , , , , , FindCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


thanks
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

It appears that Doug was able to provide the specific code.

My question about what/why still stands. You've described 'how' ... taking
values of fields and adding them to a table.

My question concerned why your users would care ... what will having this
"feature" do to make their lives/jobs easier?

I'm not asking out of prurient interest, but because knowing the business
need can lead to alternate suggestions on how to satisfy that need.

Regards

Jeff Boyce
Microsoft Office/Access MVP


deb said:
I am using the "no in list" to add the data from a form the fields that are
not in a drow down combo box.

The table has the fields Site and CustomerID that are both required along
with other fields that are not required.

I want the required fields added and the fSiteNotInList form to open so
user
can add other fields for the tSite table. When they close the
fSiteNotInList
form the data is in the f000Score form. User will not have to leave
f000Score form to add missing dropdown options. The code I entered will
only
add the one field. I am still learning so I looked at the help and am not
sure what to do. Please help

--
deb


Jeff Boyce said:
If you take a look at Access HELP for "insert", I believe you'll find the
correct syntax.

Generally (i.e., untested), you'd use something like:

"INSERT into tSite.Site, tSite.CustomerID Values ('" & NewData & "', " &
CustID & ")"

where you passed both NewData and CustID into the function.

Now, why? What are you trying to accomplish (other than inserting two
values into a table, which is more a "how" you are doing something,
rather
that why/what you want to accomplish)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


deb said:
How do i alter the below code to insert into the table tSite 2 fields
(Site
and CustomerID)
I want to use the Customer ID in the current form(f000Score.CustomerID)

The below code only inserts the field - Site and since CustomerID field
is
a
required field, it get an error that CustomerID is needed


Private Sub cboSite_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
Dim FindCriteria As String
x = MsgBox("Do you want to add this Site to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tSite ([Site]) values ('" & NewData & "')"
' strsql = "Insert Into tSite ([CustomerID]) values ('" &
[CustomerID] & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
FindCriteria = Me!cboSite.Text
DoCmd.OpenForm "fSiteNotInList", , , , , , FindCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Not in the list (combobox) 1
NotInList ComboBox Problem 5
Duplicate field entries with combobox 0
Force capitalization in NotInList event code? 3
Another Autofill Question 0
Help with strSQL 5
Not In List CBO Help 0
NotInList INSERT INTO 2 Fields HOW? 3

Top