Nothing is happening

[Jonathan Brown]

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.

 

[Jonathan Brown]

Oops, didn't mean to create a new thread. This is meant for a different one.

 

[John W. Vinson]

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.

This makes no sense to me.

It looks like you want to find those records in tblBillitsJoin with the
selected values of ClearenceNum and BilletNum, and... insert them into
tblBilletsJoin. They're already THERE, or if they aren't, you won't find them
to insert!

I'll try to find the other thread and figure out what you are intending to do.
Normally one would not need to use an Append query at all to populate a many
to many link table, as I gather this is; just use a Subform.

John W. Vinson [MVP]

 

[Jonathan Brown]

John,

Yeah, I understand. I had that code completely wrong. It's just a basic
insert statement I need. Here's the code as it is now.
_______________________________________________________________
On Error GoTo Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then
Exit Sub
Else

Dim db As Database
Dim strSQL As String

Set db = CurrentDb()
strSQL = "INSERT INTO tblBilletsJoin (ClearanceNum,BilletNum) VALUES ("" &
Me.ClearanceNum & "", "" & Me.cboBillets "")"

db.Execute strSQL

Me.lstBillet.Requery
Set db = Nothing

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click
______________________________________________________________

For some reason, nothing is happening. I'm not even getting an error
message or a compile error. I've got the proper references selected for DAO
3.6, etc. It's just that nothing happens when I click the add button.

 

[John W. Vinson]

For some reason, nothing is happening. I'm not even getting an error
message or a compile error. I've got the proper references selected for DAO
3.6, etc. It's just that nothing happens when I click the add button.

Well, in order to see any query errors you need the dbFailOnError parameter.
Let's see what else:

On Error GoTo Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then

I take it that cboBillets is in fact not NULL?

Exit Sub
Else

Dim db As Database
Dim strSQL As String

Set db = CurrentDb()
strSQL = "INSERT INTO tblBilletsJoin (ClearanceNum,BilletNum) VALUES ("" &
Me.ClearanceNum & "", "" & Me.cboBillets "")"

Are both these fields TEXT datatypes? If not you don't need the ' around them.
If you step through in debug mode, what is the actual value of strSQL?

db.Execute strSQL, dbFailOnError ' this should be added

Me.lstBillet.Requery
Set db = Nothing

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click




ONE BIG QUESTION: *why?*

It looks like all you're doing is selecting a billet in a combo box and
creating a new record.

You can do this with NO CODE AT ALL simply by using a Subform based on
tblBilletsJoin, using ClearenceNum as the master/child link field, and putting
a combo box on the subform. Could you explain why you're trying to do it the
hard way????

John W. Vinson [MVP]

 

[Jonathan Brown]

I got it figured out. Here's the code:
_________________________________________________________________

On Error Goto Err_cmdAdd_Click

If IsNull(Me.cboBillets) Then
Exit Sub
Else

Dim db as Database
Dim strsql as String

strsql = "INSERT INTO tblBilletsJoin ( ClearanceNum, BilletNum ) SELECT "
Me.ClearanceNum & " AS ClearanceNum, " & Me.cboBillets & " AS BilletNum;"
Set db = CurrentDb()

db.Execut strsql

Me.lstBillet.Requery
Me.cboBillets = Null

End If

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click
___________________________________________________________________

Good point though. That is the hard way. I wanted to just have a listbox
that showed the billets associated with their clearance. And then have them
select the new billet that they want to add to the list box and click the add
button. If they want to remove a billet they just select the billet in the
listbox and click remove. The code for the remove procedure was just like
the add procedure just with the different sql statement.

It's done and working though and I'm pretty happy with it. I occasionally
find subforms to be a bit cumbersome. But certainly not as cumbersome as the
way I was doing it. Thanks again for your help.