programatically insert record

  • Thread starter Thread starter Jonathan Brown
  • Start date Start date
J

Jonathan Brown

I have a many to many relationship between security clearances and billets.
a clearance can be associated with more than one billet and a single billet
can be associated with many clearances. That; however, may not have anything
to do with my problem.

I've built a form that has a listbox that displays the assocaited billets
for an employee's clearance. Next to it I put a drop down list box that
looks up the list of billets and stores it's value for later use. I've then
added an "add" button underneath the drop down list box. What i'd like to do
is choose a given billet from my drop down list box and then click add. It
should create a new record in my junction table with the value of the drop
down list box in the billet field.

I can't figure out the code behind my add button. I'm sure the sql
statement should be something like:

"INSERT INTO tblbilletjoin (clearancenum, billetnum) SELECT
tblBilletJoin.ClearanceNum, tblBilletsjoin.BilletNum From tblBilletsJoin
Where (((tblBilletsJoin.ClearanceNum)=[forms]![frmClearance]![ClearanceNum])
AND ((tblBilletsJoin.BilletNum)=[forms]![frmClearance]![cboBillet]));"

But I don't remember what the command is to run that sql statement. Do I
have to open a new recordsource? And then is it just me.recordsource.add
("sql statement")?

I might be totally confusing myself. I don't know.
 
Here's my code. Now, nothing is happening at all. I've checked the table
and there's no new records. Did I use the insert statement correctly?

____________________________________________________________

On Error GoTo Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then
Exit Sub
Else

CurrentProject.Connection.Execute ("INSERT INTO tblBilletsJoin
(ClearanceNum,BilletNum) " & _
"SELECT tblBilletsJoin.ClearanceNum, tblBilletsJoin.BilletNum FROM
tblBilletsJoin " & _
"WHERE tblBilletsJoin.ClearanceNum = " & Me.ClearanceNum & " AND
tblBilletsJoin.BilletNum = " & Me.cboBillets & ";")

Me.lstBillet.Requery

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click
__________________________________________________________________

Do I need to include the semicolon at the end of my sql statement? I don't
remember if I have to if I'm doing it through code.

mscertified said:
CurrentProject.Connection.Execute <sql statement>

-Dorian

Jonathan Brown said:
I have a many to many relationship between security clearances and billets.
a clearance can be associated with more than one billet and a single billet
can be associated with many clearances. That; however, may not have anything
to do with my problem.

I've built a form that has a listbox that displays the assocaited billets
for an employee's clearance. Next to it I put a drop down list box that
looks up the list of billets and stores it's value for later use. I've then
added an "add" button underneath the drop down list box. What i'd like to do
is choose a given billet from my drop down list box and then click add. It
should create a new record in my junction table with the value of the drop
down list box in the billet field.

I can't figure out the code behind my add button. I'm sure the sql
statement should be something like:

"INSERT INTO tblbilletjoin (clearancenum, billetnum) SELECT
tblBilletJoin.ClearanceNum, tblBilletsjoin.BilletNum From tblBilletsJoin
Where (((tblBilletsJoin.ClearanceNum)=[forms]![frmClearance]![ClearanceNum])
AND ((tblBilletsJoin.BilletNum)=[forms]![frmClearance]![cboBillet]));"

But I don't remember what the command is to run that sql statement. Do I
have to open a new recordsource? And then is it just me.recordsource.add
("sql statement")?

I might be totally confusing myself. I don't know.
Click to expand...
Click to expand...
 
I just changed the code to the following and it still isn't doing anything.
It's not even giving me an error message.

On Error GoTo Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then
Exit Sub
Else

Dim strSQL As String

strSQL = "INSERT INTO tblBilletsJoin (EmpNum,BilletNum) " & _
"SELECT tblBilletsJoin.EmpNum, tblBilletsJoin.BilletNum FROM tblBilletsJoin
" & _
"WHERE tblBilletsJoin.EmpNum = " & Me.EmpNum & " AND
tblBilletsJoin.BilletNum = " & Me.cboBillets & ";"

CurrentProject.Connection.Execute strSQL

Me.lstBillet.Requery

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click


Jonathan Brown said:
Here's my code. Now, nothing is happening at all. I've checked the table
and there's no new records. Did I use the insert statement correctly?

____________________________________________________________

On Error GoTo Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then
Exit Sub
Else

CurrentProject.Connection.Execute ("INSERT INTO tblBilletsJoin
(ClearanceNum,BilletNum) " & _
"SELECT tblBilletsJoin.ClearanceNum, tblBilletsJoin.BilletNum FROM
tblBilletsJoin " & _
"WHERE tblBilletsJoin.ClearanceNum = " & Me.ClearanceNum & " AND
tblBilletsJoin.BilletNum = " & Me.cboBillets & ";")

Me.lstBillet.Requery

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click
__________________________________________________________________

Do I need to include the semicolon at the end of my sql statement? I don't
remember if I have to if I'm doing it through code.

mscertified said:
CurrentProject.Connection.Execute <sql statement>

-Dorian

Jonathan Brown said:
I have a many to many relationship between security clearances and billets.
a clearance can be associated with more than one billet and a single billet
can be associated with many clearances. That; however, may not have anything
to do with my problem.

I've built a form that has a listbox that displays the assocaited billets
for an employee's clearance. Next to it I put a drop down list box that
looks up the list of billets and stores it's value for later use. I've then
added an "add" button underneath the drop down list box. What i'd like to do
is choose a given billet from my drop down list box and then click add. It
should create a new record in my junction table with the value of the drop
down list box in the billet field.

I can't figure out the code behind my add button. I'm sure the sql
statement should be something like:

"INSERT INTO tblbilletjoin (clearancenum, billetnum) SELECT
tblBilletJoin.ClearanceNum, tblBilletsjoin.BilletNum From tblBilletsJoin
Where (((tblBilletsJoin.ClearanceNum)=[forms]![frmClearance]![ClearanceNum])
AND ((tblBilletsJoin.BilletNum)=[forms]![frmClearance]![cboBillet]));"

But I don't remember what the command is to run that sql statement. Do I
have to open a new recordsource? And then is it just me.recordsource.add
("sql statement")?

I might be totally confusing myself. I don't know.
Click to expand...
Click to expand...
Click to expand...
 
Jonathan Brown said:
I just changed the code to the following and it still isn't doing anything.
It's not even giving me an error message.

On Error GoTo Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then
Exit Sub
Else

Dim strSQL As String

strSQL = "INSERT INTO tblBilletsJoin (EmpNum,BilletNum) " & _
"SELECT tblBilletsJoin.EmpNum, tblBilletsJoin.BilletNum FROM tblBilletsJoin
" & _
"WHERE tblBilletsJoin.EmpNum = " & Me.EmpNum & " AND
tblBilletsJoin.BilletNum = " & Me.cboBillets & ";"

CurrentProject.Connection.Execute strSQL

Me.lstBillet.Requery

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click


Jonathan Brown said:
Here's my code. Now, nothing is happening at all. I've checked the table
and there's no new records. Did I use the insert statement correctly?

____________________________________________________________

On Error GoTo Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then
Exit Sub
Else

CurrentProject.Connection.Execute ("INSERT INTO tblBilletsJoin
(ClearanceNum,BilletNum) " & _
"SELECT tblBilletsJoin.ClearanceNum, tblBilletsJoin.BilletNum FROM
tblBilletsJoin " & _
"WHERE tblBilletsJoin.ClearanceNum = " & Me.ClearanceNum & " AND
tblBilletsJoin.BilletNum = " & Me.cboBillets & ";")

Me.lstBillet.Requery

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click
__________________________________________________________________

Do I need to include the semicolon at the end of my sql statement? I don't
remember if I have to if I'm doing it through code.

mscertified said:
CurrentProject.Connection.Execute <sql statement>

-Dorian

:

I have a many to many relationship between security clearances and billets.
a clearance can be associated with more than one billet and a single billet
can be associated with many clearances. That; however, may not have anything
to do with my problem.

I've built a form that has a listbox that displays the assocaited billets
for an employee's clearance. Next to it I put a drop down list box that
looks up the list of billets and stores it's value for later use. I've then
added an "add" button underneath the drop down list box. What i'd like to do
is choose a given billet from my drop down list box and then click add. It
should create a new record in my junction table with the value of the drop
down list box in the billet field.

I can't figure out the code behind my add button. I'm sure the sql
statement should be something like:

"INSERT INTO tblbilletjoin (clearancenum, billetnum) SELECT
tblBilletJoin.ClearanceNum, tblBilletsjoin.BilletNum From tblBilletsJoin
Where (((tblBilletsJoin.ClearanceNum)=[forms]![frmClearance]![ClearanceNum])
AND ((tblBilletsJoin.BilletNum)=[forms]![frmClearance]![cboBillet]));"

But I don't remember what the command is to run that sql statement. Do I
have to open a new recordsource? And then is it just me.recordsource.add
("sql statement")?

I might be totally confusing myself. I don't know.
Click to expand...
Click to expand...
Click to expand...
 
The code looks ok as far as I can tell.
Use standard debugging techniques...
Display the SQL statement before it executes.
Try running the SQL standalone as a query.
Try using DEBUG to step thru the code to see what is happening.
You will learn far more by debugging this yourself that by getting a canned
answer.

-Dorian

Jonathan Brown said:
I just changed the code to the following and it still isn't doing anything.
It's not even giving me an error message.

On Error GoTo Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then
Exit Sub
Else

Dim strSQL As String

strSQL = "INSERT INTO tblBilletsJoin (EmpNum,BilletNum) " & _
"SELECT tblBilletsJoin.EmpNum, tblBilletsJoin.BilletNum FROM tblBilletsJoin
" & _
"WHERE tblBilletsJoin.EmpNum = " & Me.EmpNum & " AND
tblBilletsJoin.BilletNum = " & Me.cboBillets & ";"

CurrentProject.Connection.Execute strSQL

Me.lstBillet.Requery

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click


Jonathan Brown said:
Here's my code. Now, nothing is happening at all. I've checked the table
and there's no new records. Did I use the insert statement correctly?

____________________________________________________________

On Error GoTo Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then
Exit Sub
Else

CurrentProject.Connection.Execute ("INSERT INTO tblBilletsJoin
(ClearanceNum,BilletNum) " & _
"SELECT tblBilletsJoin.ClearanceNum, tblBilletsJoin.BilletNum FROM
tblBilletsJoin " & _
"WHERE tblBilletsJoin.ClearanceNum = " & Me.ClearanceNum & " AND
tblBilletsJoin.BilletNum = " & Me.cboBillets & ";")

Me.lstBillet.Requery

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click
__________________________________________________________________

Do I need to include the semicolon at the end of my sql statement? I don't
remember if I have to if I'm doing it through code.

mscertified said:
CurrentProject.Connection.Execute <sql statement>

-Dorian

:

I have a many to many relationship between security clearances and billets.
a clearance can be associated with more than one billet and a single billet
can be associated with many clearances. That; however, may not have anything
to do with my problem.

I've built a form that has a listbox that displays the assocaited billets
for an employee's clearance. Next to it I put a drop down list box that
looks up the list of billets and stores it's value for later use. I've then
added an "add" button underneath the drop down list box. What i'd like to do
is choose a given billet from my drop down list box and then click add. It
should create a new record in my junction table with the value of the drop
down list box in the billet field.

I can't figure out the code behind my add button. I'm sure the sql
statement should be something like:

"INSERT INTO tblbilletjoin (clearancenum, billetnum) SELECT
tblBilletJoin.ClearanceNum, tblBilletsjoin.BilletNum From tblBilletsJoin
Where (((tblBilletsJoin.ClearanceNum)=[forms]![frmClearance]![ClearanceNum])
AND ((tblBilletsJoin.BilletNum)=[forms]![frmClearance]![cboBillet]));"

But I don't remember what the command is to run that sql statement. Do I
have to open a new recordsource? And then is it just me.recordsource.add
("sql statement")?

I might be totally confusing myself. I don't know.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top