INSERT INTO

[Guest]

Greetings all. I am using Access 2003. On a form I have txthub, and
cboJSCo, among others. The form has as its record source tbljob_info and
these two controls are bound to it. I would like to, after txthub is updated
to look into another table, tblhub_log to see if the
cboJSCO.value/txthub.value combination exists. If it does not exist I want
to insert it into tblhub_log. I am still learning, so I do not know if I can
do this, but this is what I have so far.
************************************************************
Private Sub txthub_AfterUpdate()
On Error GoTo err_txthub_afterupdate

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.frmJobInfoSub1.Form.Requery

Dim cn As ADODB.Connection
Dim rsHubLog As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT co, hub_number FROM tblhub_log WHERE " & _
"co = '" & Form.[cboJSCo] & "' " & _
"AND hub_number = '" & Form.[txthub] & "' ;"

Set rsHubLog = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsHubLog.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

If strSQL not in rsHubLog Then
insert into tblhub_log(co,hub_number) strSQL
End If
rsHubLog.Close

exit_txthub_afterupdate:
Exit Sub

err_txthub_afterupdate:
MsgBox Err.description
Resume exit_txthub_afterupdate

End Sub
************************************************************
I know the above structure is wrong, mainly because it does not work, but I
was hoping it would convey what I need to do. I am stuck at this point,
thank you for any help.

 

[Douglas J. Steele]

If you're trying to refer to controls on the same form from which the code
is running, replace Form.[cboJSCo] and Form.[txthub] with Me.[cboJSCo] and
Me.[txthub]

If you're trying to refer to controls on someother open form, use
Forms!NameOfForm![cboJSCo] and Forms!NameOfForm![txthub]

 

[Guest]

Thanks Doug. I copied some code I had in another textbox, and neglected to
change it. I found a helpful post here and got it to work with the
following. As I am not well versed in this kind of thing I am assuming it
will not cause any problems as writen below. If it does I guess I will cross
that bridge when I come to it. Thanks for all your quick responses Doug.
************************************************************
Private Sub txthub_AfterUpdate()
On Error GoTo err_txthub_afterupdate

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.frmJobInfoSub1.Form.Requery

Dim cn As ADODB.Connection
Dim rsHubLog As ADODB.Recordset
Dim strSQL As String
Dim strI As String
Dim boolDupId As Boolean
strSQL = "SELECT co, hub_number FROM tblhub_log " & _
"WHERE co = '" & Me.cboJSCo.Value & "' " & _
"AND hub_number = '" & Me.txthub.Value & "' "

strI = "INSERT INTO tblhub_log(co,rte,ewo,hub_number) " & _
"SELECT co,rte,ewo,hub# FROM tbljob_info " & _
"WHERE ewo = '" & Me.txtEwo.Value & "' "

boolDupId = False
Set rsHubLog = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsHubLog.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic
If rsHubLog.EOF Then
boolDupId = False
DoCmd.RunSQL strI
MsgBox "Hub Log updated", vbExclamation
End If
rsHubLog.Close

Cancel = boolDupId

exit_txthub_afterupdate:

************************************************************

If you're trying to refer to controls on the same form from which the code
is running, replace Form.[cboJSCo] and Form.[txthub] with Me.[cboJSCo] and
Me.[txthub]

If you're trying to refer to controls on someother open form, use
Forms!NameOfForm![cboJSCo] and Forms!NameOfForm![txthub]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Greetings all. I am using Access 2003. On a form I have txthub, and
cboJSCo, among others. The form has as its record source tbljob_info and
these two controls are bound to it. I would like to, after txthub is
updated
to look into another table, tblhub_log to see if the
cboJSCO.value/txthub.value combination exists. If it does not exist I
want
to insert it into tblhub_log. I am still learning, so I do not know if I
can
do this, but this is what I have so far.
************************************************************
Private Sub txthub_AfterUpdate()
On Error GoTo err_txthub_afterupdate

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.frmJobInfoSub1.Form.Requery

Dim cn As ADODB.Connection
Dim rsHubLog As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT co, hub_number FROM tblhub_log WHERE " & _
"co = '" & Form.[cboJSCo] & "' " & _
"AND hub_number = '" & Form.[txthub] & "' ;"

Set rsHubLog = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsHubLog.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

If strSQL not in rsHubLog Then
insert into tblhub_log(co,hub_number) strSQL
End If
rsHubLog.Close

exit_txthub_afterupdate:
Exit Sub

err_txthub_afterupdate:
MsgBox Err.description
Resume exit_txthub_afterupdate

End Sub
************************************************************
I know the above structure is wrong, mainly because it does not work, but
I
was hoping it would convey what I need to do. I am stuck at this point,
thank you for any help.

 

[Guest]

Well, Doug, I have come to a problem. I am trying to base an if/then
statement on whether or not the ADO recordset is null. Here it is.
************************************************************
Dim cn As ADODB.Connection
Dim rsHubLocation As ADODB.Recordset
Dim strS As String
Dim strU As String
strS = "SELECT hub_location FROM tblhub_log " & _
"WHERE co = '" & Me.cboCO.Value & "' " & _
"AND hub_number = '" & Me.[Hub#].Value & "' "

strU = "UPDATE tblhub_log " & _
"SET hub_location = '" & Me.location.Value & "' " & _
"WHERE co = '" & Me.cboCO.Value & "' " & _
"AND hub_number = '" & Me.[Hub#].Value & "' "

Set rsHubLocation = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsHubLog.Open strS, cn, adOpenForwardOnly, adLockOptimistic
If rsHubLog.IsNull Then
DoCmd.RunSQL strU
MsgBox "Hub location updated"
End If
rsHubLog.Close
************************************************************
I am not sure how to run strU if strS is null. I am not sure either that if
the field hub_location in tblhub_log is null the rsHubLocation will be null,
or will it have a value. If it returns a value of hub_location is null based

If you're trying to refer to controls on the same form from which the code
is running, replace Form.[cboJSCo] and Form.[txthub] with Me.[cboJSCo] and
Me.[txthub]

If you're trying to refer to controls on someother open form, use
Forms!NameOfForm![cboJSCo] and Forms!NameOfForm![txthub]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Greetings all. I am using Access 2003. On a form I have txthub, and
cboJSCo, among others. The form has as its record source tbljob_info and
these two controls are bound to it. I would like to, after txthub is
updated
to look into another table, tblhub_log to see if the
cboJSCO.value/txthub.value combination exists. If it does not exist I
want
to insert it into tblhub_log. I am still learning, so I do not know if I
can
do this, but this is what I have so far.
************************************************************
Private Sub txthub_AfterUpdate()
On Error GoTo err_txthub_afterupdate

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.frmJobInfoSub1.Form.Requery

Dim cn As ADODB.Connection
Dim rsHubLog As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT co, hub_number FROM tblhub_log WHERE " & _
"co = '" & Form.[cboJSCo] & "' " & _
"AND hub_number = '" & Form.[txthub] & "' ;"

Set rsHubLog = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsHubLog.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

If strSQL not in rsHubLog Then
insert into tblhub_log(co,hub_number) strSQL
End If
rsHubLog.Close

exit_txthub_afterupdate:
Exit Sub

err_txthub_afterupdate:
MsgBox Err.description
Resume exit_txthub_afterupdate

End Sub
************************************************************
I know the above structure is wrong, mainly because it does not work, but
I
was hoping it would convey what I need to do. I am stuck at this point,
thank you for any help.

 

[Guest]

I realize I neglected to change all instances of rsHubLog to rsHubLocation.
I am no longer getting the "object required" error, but I still have the same
questions as below.

Well, Doug, I have come to a problem. I am trying to base an if/then
statement on whether or not the ADO recordset is null. Here it is.
************************************************************
Dim cn As ADODB.Connection
Dim rsHubLocation As ADODB.Recordset
Dim strS As String
Dim strU As String
strS = "SELECT hub_location FROM tblhub_log " & _
"WHERE co = '" & Me.cboCO.Value & "' " & _
"AND hub_number = '" & Me.[Hub#].Value & "' "

strU = "UPDATE tblhub_log " & _
"SET hub_location = '" & Me.location.Value & "' " & _
"WHERE co = '" & Me.cboCO.Value & "' " & _
"AND hub_number = '" & Me.[Hub#].Value & "' "

Set rsHubLocation = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsHubLog.Open strS, cn, adOpenForwardOnly, adLockOptimistic
If rsHubLog.IsNull Then
DoCmd.RunSQL strU
MsgBox "Hub location updated"
End If
rsHubLog.Close
************************************************************
I am not sure how to run strU if strS is null. I am not sure either that if
the field hub_location in tblhub_log is null the rsHubLocation will be null,
or will it have a value. If it returns a value of hub_location is null based

If you're trying to refer to controls on the same form from which the code
is running, replace Form.[cboJSCo] and Form.[txthub] with Me.[cboJSCo] and
Me.[txthub]

If you're trying to refer to controls on someother open form, use
Forms!NameOfForm![cboJSCo] and Forms!NameOfForm![txthub]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Greetings all. I am using Access 2003. On a form I have txthub, and
cboJSCo, among others. The form has as its record source tbljob_info and
these two controls are bound to it. I would like to, after txthub is
updated
to look into another table, tblhub_log to see if the
cboJSCO.value/txthub.value combination exists. If it does not exist I
want
to insert it into tblhub_log. I am still learning, so I do not know if I
can
do this, but this is what I have so far.
************************************************************
Private Sub txthub_AfterUpdate()
On Error GoTo err_txthub_afterupdate

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.frmJobInfoSub1.Form.Requery

Dim cn As ADODB.Connection
Dim rsHubLog As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT co, hub_number FROM tblhub_log WHERE " & _
"co = '" & Form.[cboJSCo] & "' " & _
"AND hub_number = '" & Form.[txthub] & "' ;"

Set rsHubLog = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsHubLog.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

If strSQL not in rsHubLog Then
insert into tblhub_log(co,hub_number) strSQL
End If
rsHubLog.Close

exit_txthub_afterupdate:
Exit Sub

err_txthub_afterupdate:
MsgBox Err.description
Resume exit_txthub_afterupdate

End Sub
************************************************************
I know the above structure is wrong, mainly because it does not work, but
I
was hoping it would convey what I need to do. I am stuck at this point,
thank you for any help.

 

[Douglas J. Steele]

To check whether a recordset contains any records, look at the EOF and BOF
properties:

If (rsHubLog.EOF = True) And (rsHubLog.BOF = True) Then
DoCmd.RunSQL strU
MsgBox "Hub location updated"
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well, Doug, I have come to a problem. I am trying to base an if/then
statement on whether or not the ADO recordset is null. Here it is.
************************************************************
Dim cn As ADODB.Connection
Dim rsHubLocation As ADODB.Recordset
Dim strS As String
Dim strU As String
strS = "SELECT hub_location FROM tblhub_log " & _
"WHERE co = '" & Me.cboCO.Value & "' " & _
"AND hub_number = '" & Me.[Hub#].Value & "' "

strU = "UPDATE tblhub_log " & _
"SET hub_location = '" & Me.location.Value & "' " & _
"WHERE co = '" & Me.cboCO.Value & "' " & _
"AND hub_number = '" & Me.[Hub#].Value & "' "

Set rsHubLocation = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsHubLog.Open strS, cn, adOpenForwardOnly, adLockOptimistic
If rsHubLog.IsNull Then
DoCmd.RunSQL strU
MsgBox "Hub location updated"
End If
rsHubLog.Close
************************************************************
I am not sure how to run strU if strS is null. I am not sure either that
if
the field hub_location in tblhub_log is null the rsHubLocation will be
null,
or will it have a value. If it returns a value of hub_location is null
based
on strS, how can I reference that value to run strU? Thank you for all
your
help.

If you're trying to refer to controls on the same form from which the
code
is running, replace Form.[cboJSCo] and Form.[txthub] with Me.[cboJSCo]
and
Me.[txthub]

If you're trying to refer to controls on someother open form, use
Forms!NameOfForm![cboJSCo] and Forms!NameOfForm![txthub]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Greetings all. I am using Access 2003. On a form I have txthub, and
cboJSCo, among others. The form has as its record source tbljob_info
and
these two controls are bound to it. I would like to, after txthub is
updated
to look into another table, tblhub_log to see if the
cboJSCO.value/txthub.value combination exists. If it does not exist I
want
to insert it into tblhub_log. I am still learning, so I do not know if
I
can
do this, but this is what I have so far.
************************************************************
Private Sub txthub_AfterUpdate()
On Error GoTo err_txthub_afterupdate

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.frmJobInfoSub1.Form.Requery

Dim cn As ADODB.Connection
Dim rsHubLog As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT co, hub_number FROM tblhub_log WHERE " & _
"co = '" & Form.[cboJSCo] & "' " & _
"AND hub_number = '" & Form.[txthub] & "' ;"

Set rsHubLog = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsHubLog.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

If strSQL not in rsHubLog Then
insert into tblhub_log(co,hub_number) strSQL
End If
rsHubLog.Close

exit_txthub_afterupdate:
Exit Sub

err_txthub_afterupdate:
MsgBox Err.description
Resume exit_txthub_afterupdate

End Sub
************************************************************
I know the above structure is wrong, mainly because it does not work,
but
I
was hoping it would convey what I need to do. I am stuck at this
point,
thank you for any help.

 

[onedaywhen]

I would like to, after txthub is updated
to look into another table, tblhub_log to see if the
cboJSCO.value/txthub.value combination exists. If it does not exist I want
to insert it into tblhub_log. I am still learning, so I do not know if I can
do this, but this is what I have so far.

Forget the recordset. This is usually done in SQL using NOT EXISTS
e.g. (untested)

strSQL = _
"insert into tblhub_log (co,hub_number)" & _
" SELECT DISTINCT '" & Form.[cboJSCo] & "', '" & _
Form.[txthub] & "'" & _
" FROM tblhub_log AS T1" & _
" WHERE NOT EXISTS (" & _
" SELECT * FROM tblhub_log AS T2" & _
" WHERE T2.co = '" & Form.[cboJSCo] & "'" & _
" AND T2.hub_number = '" & Form.[txthub] & "');"

Obviously tblhub_log AS T1 cannot be empty; the table used here is
arbitrary (unlike tblhub_log AS T2) so consider using an existing
auxiliary table or a bespoke load table.

Jamie.

--