Updating table based on data entry

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I have a data entry form which records status information
to a status table.
For some particular status entries, I also need to update
another table. I'm thinking that using the AfterUpdate
event should work for this...so I would have code to check
if a certain status condition is met, and then update my
other table if it is true. I know how to do most of this,
but the tricky part (for me) is finding the right place in
the other table to make the update. Basically, I need to
compare one field from the status entry to the other table
and then update a field in that record based on another
field from the status entry. Below is the beginnings of
the code that I think will work. I've put some comments
in ALL CAPS where I've got uncertainties:

Private Sub Form_AfterUpdate()
'Check for proper status condition
If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

HERE IS WHERE I RUN IN TO TROUBLE. I NEED TO SEARCH
TABLE "TEST" TO FIND WHERE LOT MATCHES A FIELD CALLED
BIN. IF LOT MATCHES BIN, THEN I WANT THE "VESSEL" VALUE
TO BE UPDATED IN THE FIELD IN "TEST" CALLED "VessNum".
THE BELOW CODE SHOULD UPDATE THE FIELD, BUT AGAIN, I DON'T
KNOW HOW TO FIND THE RIGHT FIELD.

Set rs = DBEngine(0)(0).OpenRecordset("Test",
dbOpenDynaset)
With rs
![VessNum] = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

Thanks in advance for any help.
Note: if there is a better way to do this, please let me
know.
 
Hi Todd,

What I think you need to do is build the SQL text for an update query and
then run it.

If I have understood your post - something like:

Dim strSQLText As String
strSQLText = "UPDATE Test SET Test.VessNum = '" & Vessel & "'" & vbCrLf
strSQLText = strSQLText & " WHERE (((Test.BIN)='" & LOT & "'));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQLText
DoCmd.SetWarnings True

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/
 
Todd,

Instead of opening the whole table as a recordset, open a query on it where
BIN = LOT:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM TEST WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM TEST WHERE BIN ='" & LOT & "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
With rs
.Fields("VessNum") = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

This code assumes ther will only ever be one matching record. If tehre are
more then you will need a Do Until rs.EOF loop, and a .MoveNext after each
..Update.

Alternatively, you could use an update query in SQL instead of the
recordset.

HTH,
Nikos
 
Awesome. This works GREAT.

One extra question for you. If there is not a match of
BIN and LOT, I get an error because it's trying to put the
VessNum in but it hasn't found a place to put it. I guess
I need to do a check somehow, and say if the BIN isn't
there, do nothing. How do I do this?
-----Original Message-----
Todd,

Instead of opening the whole table as a recordset, open a query on it where
BIN = LOT:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM TEST WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM TEST WHERE BIN ='" & LOT & "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
With rs
.Fields("VessNum") = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

This code assumes ther will only ever be one matching record. If tehre are
more then you will need a Do Until rs.EOF loop, and a .MoveNext after each
..Update.

Alternatively, you could use an update query in SQL instead of the
recordset.

HTH,
Nikos




I have a data entry form which records status information
to a status table.
For some particular status entries, I also need to update
another table. I'm thinking that using the AfterUpdate
event should work for this...so I would have code to check
if a certain status condition is met, and then update my
other table if it is true. I know how to do most of this,
but the tricky part (for me) is finding the right place in
the other table to make the update. Basically, I need to
compare one field from the status entry to the other table
and then update a field in that record based on another
field from the status entry. Below is the beginnings of
the code that I think will work. I've put some comments
in ALL CAPS where I've got uncertainties:

Private Sub Form_AfterUpdate()
'Check for proper status condition
If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

HERE IS WHERE I RUN IN TO TROUBLE. I NEED TO SEARCH
TABLE "TEST" TO FIND WHERE LOT MATCHES A FIELD CALLED
BIN. IF LOT MATCHES BIN, THEN I WANT THE "VESSEL" VALUE
TO BE UPDATED IN THE FIELD IN "TEST" CALLED "VessNum".
THE BELOW CODE SHOULD UPDATE THE FIELD, BUT AGAIN, I DON'T
KNOW HOW TO FIND THE RIGHT FIELD.

Set rs = DBEngine(0)(0).OpenRecordset("Test",
dbOpenDynaset)
With rs
![VessNum] = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

Thanks in advance for any help.
Note: if there is a better way to do this, please let me
know.
Click to expand...


.
Click to expand...
 
Sorry, just one more question.
I just realized that in my actual database, there is a
space in the name of the actual table that I'm using (TEST
was just a dummy name for this posting). How do I account
for the space in the code where I reference the table, I'm
getting an error....eg strSQL = "SELECT VessNum FROM TEST
WHERE BIN ='" & LOT & "'"...instead of TEST, say the table
is named TEST TABLE...what do I do?
-----Original Message-----
Awesome. This works GREAT.

One extra question for you. If there is not a match of
BIN and LOT, I get an error because it's trying to put the
VessNum in but it hasn't found a place to put it. I guess
I need to do a check somehow, and say if the BIN isn't
there, do nothing. How do I do this?
-----Original Message-----
Todd,

Instead of opening the whole table as a recordset, open
Click to expand...
a
query on it where
BIN = LOT:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM TEST WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM TEST WHERE BIN ='" & LOT & "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
With rs
.Fields("VessNum") = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

This code assumes ther will only ever be one matching record. If tehre are
more then you will need a Do Until rs.EOF loop, and a .MoveNext after each
..Update.

Alternatively, you could use an update query in SQL instead of the
recordset.

HTH,
Nikos
Click to expand...
place
in
the other table to make the update. Basically, I need to
compare one field from the status entry to the other table
and then update a field in that record based on another
field from the status entry. Below is the beginnings of
the code that I think will work. I've put some comments
in ALL CAPS where I've got uncertainties:

Private Sub Form_AfterUpdate()
'Check for proper status condition
If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

HERE IS WHERE I RUN IN TO TROUBLE. I NEED TO SEARCH
TABLE "TEST" TO FIND WHERE LOT MATCHES A FIELD CALLED
BIN. IF LOT MATCHES BIN, THEN I WANT THE "VESSEL" VALUE
TO BE UPDATED IN THE FIELD IN "TEST" CALLED "VessNum".
THE BELOW CODE SHOULD UPDATE THE FIELD, BUT AGAIN, I DON'T
KNOW HOW TO FIND THE RIGHT FIELD.

Set rs = DBEngine(0)(0).OpenRecordset("Test",
dbOpenDynaset)
With rs
![VessNum] = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

Thanks in advance for any help.
Note: if there is a better way to do this, please let me
know.
Click to expand...


.
Click to expand...
.
Click to expand...
 
Todd,

See modifications to code below, that answer both questions:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN ='" & LOT & "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then GoTo No_Record
With rs
..Fields("VessNum") = Vessel
..Update
End With

No_Record:
rs.Close
Set rs = Nothing
End If
End Sub



Note that thw SQL update query alternative would not require a record check.



HTH,

Nikos



Todd said:
Sorry, just one more question.
I just realized that in my actual database, there is a
space in the name of the actual table that I'm using (TEST
was just a dummy name for this posting). How do I account
for the space in the code where I reference the table, I'm
getting an error....eg strSQL = "SELECT VessNum FROM TEST
WHERE BIN ='" & LOT & "'"...instead of TEST, say the table
is named TEST TABLE...what do I do?
-----Original Message-----
Awesome. This works GREAT.

One extra question for you. If there is not a match of
BIN and LOT, I get an error because it's trying to put the
VessNum in but it hasn't found a place to put it. I guess
I need to do a check somehow, and say if the BIN isn't
there, do nothing. How do I do this?
-----Original Message-----
Todd,

Instead of opening the whole table as a recordset, open
Click to expand...
a
query on it where
BIN = LOT:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM TEST WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM TEST WHERE BIN ='" & LOT & "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
With rs
.Fields("VessNum") = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

This code assumes ther will only ever be one matching record. If tehre are
more then you will need a Do Until rs.EOF loop, and a .MoveNext after each
..Update.

Alternatively, you could use an update query in SQL instead of the
recordset.

HTH,
Nikos




I have a data entry form which records status information
to a status table.
For some particular status entries, I also need to update
another table. I'm thinking that using the AfterUpdate
event should work for this...so I would have code to check
if a certain status condition is met, and then update my
other table if it is true. I know how to do most of this,
but the tricky part (for me) is finding the right
Click to expand...
place
in
the other table to make the update. Basically, I need to
compare one field from the status entry to the other table
and then update a field in that record based on another
field from the status entry. Below is the beginnings of
the code that I think will work. I've put some comments
in ALL CAPS where I've got uncertainties:

Private Sub Form_AfterUpdate()
'Check for proper status condition
If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

HERE IS WHERE I RUN IN TO TROUBLE. I NEED TO SEARCH
TABLE "TEST" TO FIND WHERE LOT MATCHES A FIELD CALLED
BIN. IF LOT MATCHES BIN, THEN I WANT THE "VESSEL" VALUE
TO BE UPDATED IN THE FIELD IN "TEST" CALLED "VessNum".
THE BELOW CODE SHOULD UPDATE THE FIELD, BUT AGAIN, I DON'T
KNOW HOW TO FIND THE RIGHT FIELD.

Set rs = DBEngine(0)(0).OpenRecordset("Test",
dbOpenDynaset)
With rs
![VessNum] = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

Thanks in advance for any help.
Note: if there is a better way to do this, please let me
know.


.
Click to expand...
.
Click to expand...
Click to expand...
 
Thanks again for the help.
I'm still getting an error.
Here's the exact code that I have (by they way BIN is
text).

Private Sub Form_AfterUpdate()
If (Me.Combo18 = "Frozen" And Me.Combo34 = "CR-10") Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM [BATCH HISTORY] WHERE BIN
='" & LOT & "'"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL,
dbOpenDynaset)
If rs.RecordCount = 0 Then GoTo No_Record
With rs
.Edit
.Fields("VessNum") = Vessel
.Update
End With
No_Record:
rs.Close
Set rs = Nothing
End If
End Sub

I've run this fine on a table called TEST instead of BATCH
HISTORY and it works perfectly. When I try to run it on
my actual table, I've put the table name in brackets as
per the above. I keep getting an error that says
RUN TIME ERROR 3061
TOO FEW PARAMETERS. EXPECTED 1.

When I click on Debug, the line Set rs = DBEngine(0)
(0).OpenRecordset(strSQL, dbOpenDynaset) is highlighted.

Any thoughts?

-----Original Message-----
Todd,

See modifications to code below, that answer both questions:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN ='" & LOT & "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then GoTo No_Record
With rs
..Fields("VessNum") = Vessel
..Update
End With

No_Record:
rs.Close
Set rs = Nothing
End If
End Sub



Note that thw SQL update query alternative would not require a record check.



HTH,

Nikos



Sorry, just one more question.
I just realized that in my actual database, there is a
space in the name of the actual table that I'm using (TEST
was just a dummy name for this posting). How do I account
for the space in the code where I reference the table, I'm
getting an error....eg strSQL = "SELECT VessNum FROM TEST
WHERE BIN ='" & LOT & "'"...instead of TEST, say the table
is named TEST TABLE...what do I do?
-----Original Message-----
Awesome. This works GREAT.

One extra question for you. If there is not a match of
BIN and LOT, I get an error because it's trying to put the
VessNum in but it hasn't found a place to put it. I guess
I need to do a check somehow, and say if the BIN isn't
there, do nothing. How do I do this?

-----Original Message-----
Todd,

Instead of opening the whole table as a recordset,
Click to expand...
open
a
query on it where
BIN = LOT:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM TEST WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM TEST WHERE BIN ='" & LOT
& "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL,
dbOpenDynaset)
With rs
.Fields("VessNum") = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

This code assumes ther will only ever be one matching
record. If tehre are
more then you will need a Do Until rs.EOF loop, and
a .MoveNext after each
..Update.

Alternatively, you could use an update query in SQL
instead of the
recordset.

HTH,
Nikos




message
I have a data entry form which records status
information
to a status table.
For some particular status entries, I also need to
update
another table. I'm thinking that using the AfterUpdate
event should work for this...so I would have code to
check
if a certain status condition is met, and then
Click to expand...
update
my
other table if it is true. I know how to do most of
this,
but the tricky part (for me) is finding the right place
in
the other table to make the update. Basically, I need
to
compare one field from the status entry to the other
table
and then update a field in that record based on another
field from the status entry. Below is the
Click to expand...
beginnings
of
the code that I think will work. I've put some comments
in ALL CAPS where I've got uncertainties:

Private Sub Form_AfterUpdate()
'Check for proper status condition
If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

HERE IS WHERE I RUN IN TO TROUBLE. I NEED TO SEARCH
TABLE "TEST" TO FIND WHERE LOT MATCHES A FIELD CALLED
BIN. IF LOT MATCHES BIN, THEN I WANT THE "VESSEL" VALUE
TO BE UPDATED IN THE FIELD IN "TEST" CALLED "VessNum".
THE BELOW CODE SHOULD UPDATE THE FIELD, BUT AGAIN, I
DON'T
KNOW HOW TO FIND THE RIGHT FIELD.

Set rs = DBEngine(0)(0).OpenRecordset("Test",
dbOpenDynaset)
With rs
![VessNum] = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

Thanks in advance for any help.
Note: if there is a better way to do this, please
Click to expand...
let
me
know.


.

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


.
Click to expand...
 
Todd,

That's funny, I don't know why... you are not a victim of NG wrapping by the
way, by any chance? Is your strSQL =... all in one line, including the final
& "'"?

Nikos

Todd said:
Thanks again for the help.
I'm still getting an error.
Here's the exact code that I have (by they way BIN is
text).

Private Sub Form_AfterUpdate()
If (Me.Combo18 = "Frozen" And Me.Combo34 = "CR-10") Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM [BATCH HISTORY] WHERE BIN
='" & LOT & "'"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL,
dbOpenDynaset)
If rs.RecordCount = 0 Then GoTo No_Record
With rs
.Edit
.Fields("VessNum") = Vessel
.Update
End With
No_Record:
rs.Close
Set rs = Nothing
End If
End Sub

I've run this fine on a table called TEST instead of BATCH
HISTORY and it works perfectly. When I try to run it on
my actual table, I've put the table name in brackets as
per the above. I keep getting an error that says
RUN TIME ERROR 3061
TOO FEW PARAMETERS. EXPECTED 1.

When I click on Debug, the line Set rs = DBEngine(0)
(0).OpenRecordset(strSQL, dbOpenDynaset) is highlighted.

Any thoughts?

-----Original Message-----
Todd,

See modifications to code below, that answer both questions:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN ='" & LOT & "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then GoTo No_Record
With rs
..Fields("VessNum") = Vessel
..Update
End With

No_Record:
rs.Close
Set rs = Nothing
End If
End Sub



Note that thw SQL update query alternative would not require a record check.



HTH,

Nikos



Sorry, just one more question.
I just realized that in my actual database, there is a
space in the name of the actual table that I'm using (TEST
was just a dummy name for this posting). How do I account
for the space in the code where I reference the table, I'm
getting an error....eg strSQL = "SELECT VessNum FROM TEST
WHERE BIN ='" & LOT & "'"...instead of TEST, say the table
is named TEST TABLE...what do I do?

-----Original Message-----
Awesome. This works GREAT.

One extra question for you. If there is not a match of
BIN and LOT, I get an error because it's trying to put
the
VessNum in but it hasn't found a place to put it. I
guess
I need to do a check somehow, and say if the BIN isn't
there, do nothing. How do I do this?

-----Original Message-----
Todd,

Instead of opening the whole table as a recordset, open
a
query on it where
BIN = LOT:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM TEST WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM TEST WHERE BIN ='" & LOT
& "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL,
dbOpenDynaset)
With rs
.Fields("VessNum") = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

This code assumes ther will only ever be one matching
record. If tehre are
more then you will need a Do Until rs.EOF loop, and
a .MoveNext after each
..Update.

Alternatively, you could use an update query in SQL
instead of the
recordset.

HTH,
Nikos




message
I have a data entry form which records status
information
to a status table.
For some particular status entries, I also need to
update
another table. I'm thinking that using the AfterUpdate
event should work for this...so I would have code to
check
if a certain status condition is met, and then update
my
other table if it is true. I know how to do most of
this,
but the tricky part (for me) is finding the right
place
in
the other table to make the update. Basically, I need
to
compare one field from the status entry to the other
table
and then update a field in that record based on another
field from the status entry. Below is the beginnings
of
the code that I think will work. I've put some
comments
in ALL CAPS where I've got uncertainties:

Private Sub Form_AfterUpdate()
'Check for proper status condition
If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

HERE IS WHERE I RUN IN TO TROUBLE. I NEED TO SEARCH
TABLE "TEST" TO FIND WHERE LOT MATCHES A FIELD CALLED
BIN. IF LOT MATCHES BIN, THEN I WANT THE "VESSEL"
VALUE
TO BE UPDATED IN THE FIELD IN "TEST" CALLED "VessNum".
THE BELOW CODE SHOULD UPDATE THE FIELD, BUT AGAIN, I
DON'T
KNOW HOW TO FIND THE RIGHT FIELD.

Set rs = DBEngine(0)(0).OpenRecordset("Test",
dbOpenDynaset)
With rs
![VessNum] = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

Thanks in advance for any help.
Note: if there is a better way to do this, please let
me
know.


.

.
Click to expand...


.
Click to expand...
Click to expand...
 
No, its not wrapped. The whole statement is on a single
line.

Any ideas?
-----Original Message-----
Todd,

That's funny, I don't know why... you are not a victim of NG wrapping by the
way, by any chance? Is your strSQL =... all in one line, including the final
& "'"?

Nikos

Thanks again for the help.
I'm still getting an error.
Here's the exact code that I have (by they way BIN is
text).

Private Sub Form_AfterUpdate()
If (Me.Combo18 = "Frozen" And Me.Combo34 = "CR-10") Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM [BATCH HISTORY] WHERE BIN
='" & LOT & "'"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL,
dbOpenDynaset)
If rs.RecordCount = 0 Then GoTo No_Record
With rs
.Edit
.Fields("VessNum") = Vessel
.Update
End With
No_Record:
rs.Close
Set rs = Nothing
End If
End Sub

I've run this fine on a table called TEST instead of BATCH
HISTORY and it works perfectly. When I try to run it on
my actual table, I've put the table name in brackets as
per the above. I keep getting an error that says
RUN TIME ERROR 3061
TOO FEW PARAMETERS. EXPECTED 1.

When I click on Debug, the line Set rs = DBEngine(0)
(0).OpenRecordset(strSQL, dbOpenDynaset) is highlighted.

Any thoughts?

-----Original Message-----
Todd,

See modifications to code below, that answer both questions:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN
Click to expand...
=" &
LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN
Click to expand...
='"
& LOT & "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then GoTo No_Record
With rs
..Fields("VessNum") = Vessel
..Update
End With

No_Record:
rs.Close
Set rs = Nothing
End If
End Sub



Note that thw SQL update query alternative would not require a record check.



HTH,

Nikos



Sorry, just one more question.
I just realized that in my actual database, there is a
space in the name of the actual table that I'm using (TEST
was just a dummy name for this posting). How do I account
for the space in the code where I reference the
Click to expand...
table,
I'm
getting an error....eg strSQL = "SELECT VessNum FROM TEST
WHERE BIN ='" & LOT & "'"...instead of TEST, say the table
is named TEST TABLE...what do I do?

-----Original Message-----
Awesome. This works GREAT.

One extra question for you. If there is not a match of
BIN and LOT, I get an error because it's trying to put
the
VessNum in but it hasn't found a place to put it. I
guess
I need to do a check somehow, and say if the BIN isn't
there, do nothing. How do I do this?

-----Original Message-----
Todd,

Instead of opening the whole table as a recordset, open
a
query on it where
BIN = LOT:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM TEST WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM TEST WHERE BIN ='" & LOT
& "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL,
dbOpenDynaset)
With rs
.Fields("VessNum") = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

This code assumes ther will only ever be one matching
record. If tehre are
more then you will need a Do Until rs.EOF loop, and
a .MoveNext after each
..Update.

Alternatively, you could use an update query in SQL
instead of the
recordset.

HTH,
Nikos




message
I have a data entry form which records status
information
to a status table.
For some particular status entries, I also need to
update
another table. I'm thinking that using the AfterUpdate
event should work for this...so I would have code to
check
if a certain status condition is met, and then update
my
other table if it is true. I know how to do most of
this,
but the tricky part (for me) is finding the right
place
in
the other table to make the update. Basically, I need
to
compare one field from the status entry to the other
table
and then update a field in that record based on another
field from the status entry. Below is the beginnings
of
the code that I think will work. I've put some
comments
in ALL CAPS where I've got uncertainties:

Private Sub Form_AfterUpdate()
'Check for proper status condition
If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

HERE IS WHERE I RUN IN TO TROUBLE. I NEED TO SEARCH
TABLE "TEST" TO FIND WHERE LOT MATCHES A FIELD CALLED
BIN. IF LOT MATCHES BIN, THEN I WANT THE "VESSEL"
VALUE
TO BE UPDATED IN THE FIELD IN "TEST" CALLED "VessNum".
THE BELOW CODE SHOULD UPDATE THE FIELD, BUT AGAIN, I
DON'T
KNOW HOW TO FIND THE RIGHT FIELD.

Set rs = DBEngine(0)(0).OpenRecordset("Test",
dbOpenDynaset)
With rs
![VessNum] = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

Thanks in advance for any help.
Note: if there is a better way to do this, please let
me
know.


.

.



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


.
Click to expand...
 
Forget my last question. I just realized that I had the
Vessel field spelled differently between my BATCH HISTORY
table and my TEST table. I fixed it, and it runs
perfectly now.

THANKS FOR YOUR HELP. IT IS MUCH APPRECIATED.
-----Original Message-----
Todd,

That's funny, I don't know why... you are not a victim of NG wrapping by the
way, by any chance? Is your strSQL =... all in one line, including the final
& "'"?

Nikos

Thanks again for the help.
I'm still getting an error.
Here's the exact code that I have (by they way BIN is
text).

Private Sub Form_AfterUpdate()
If (Me.Combo18 = "Frozen" And Me.Combo34 = "CR-10") Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM [BATCH HISTORY] WHERE BIN
='" & LOT & "'"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL,
dbOpenDynaset)
If rs.RecordCount = 0 Then GoTo No_Record
With rs
.Edit
.Fields("VessNum") = Vessel
.Update
End With
No_Record:
rs.Close
Set rs = Nothing
End If
End Sub

I've run this fine on a table called TEST instead of BATCH
HISTORY and it works perfectly. When I try to run it on
my actual table, I've put the table name in brackets as
per the above. I keep getting an error that says
RUN TIME ERROR 3061
TOO FEW PARAMETERS. EXPECTED 1.

When I click on Debug, the line Set rs = DBEngine(0)
(0).OpenRecordset(strSQL, dbOpenDynaset) is highlighted.

Any thoughts?

-----Original Message-----
Todd,

See modifications to code below, that answer both questions:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN
Click to expand...
=" &
LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM [TEST TABLE] WHERE BIN
Click to expand...
='"
& LOT & "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then GoTo No_Record
With rs
..Fields("VessNum") = Vessel
..Update
End With

No_Record:
rs.Close
Set rs = Nothing
End If
End Sub



Note that thw SQL update query alternative would not require a record check.



HTH,

Nikos



Sorry, just one more question.
I just realized that in my actual database, there is a
space in the name of the actual table that I'm using (TEST
was just a dummy name for this posting). How do I account
for the space in the code where I reference the
Click to expand...
table,
I'm
getting an error....eg strSQL = "SELECT VessNum FROM TEST
WHERE BIN ='" & LOT & "'"...instead of TEST, say the table
is named TEST TABLE...what do I do?

-----Original Message-----
Awesome. This works GREAT.

One extra question for you. If there is not a match of
BIN and LOT, I get an error because it's trying to put
the
VessNum in but it hasn't found a place to put it. I
guess
I need to do a check somehow, and say if the BIN isn't
there, do nothing. How do I do this?

-----Original Message-----
Todd,

Instead of opening the whole table as a recordset, open
a
query on it where
BIN = LOT:

If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

strSQL = "SELECT VessNum FROM TEST WHERE BIN =" & LOT
(if BIN is numeric, or)
strSQL = "SELECT VessNum FROM TEST WHERE BIN ='" & LOT
& "'"
(if BIN is text)

Set rs = DBEngine(0)(0).OpenRecordset(strSQL,
dbOpenDynaset)
With rs
.Fields("VessNum") = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

This code assumes ther will only ever be one matching
record. If tehre are
more then you will need a Do Until rs.EOF loop, and
a .MoveNext after each
..Update.

Alternatively, you could use an update query in SQL
instead of the
recordset.

HTH,
Nikos




message
I have a data entry form which records status
information
to a status table.
For some particular status entries, I also need to
update
another table. I'm thinking that using the AfterUpdate
event should work for this...so I would have code to
check
if a certain status condition is met, and then update
my
other table if it is true. I know how to do most of
this,
but the tricky part (for me) is finding the right
place
in
the other table to make the update. Basically, I need
to
compare one field from the status entry to the other
table
and then update a field in that record based on another
field from the status entry. Below is the beginnings
of
the code that I think will work. I've put some
comments
in ALL CAPS where I've got uncertainties:

Private Sub Form_AfterUpdate()
'Check for proper status condition
If Me.Combo18 = "Frozen" Then
Dim rs As DAO.Recordset
Dim LOT As String
Dim Vessel As String

LOT = Me.Combo23
Vessel = Me.Combo12

HERE IS WHERE I RUN IN TO TROUBLE. I NEED TO SEARCH
TABLE "TEST" TO FIND WHERE LOT MATCHES A FIELD CALLED
BIN. IF LOT MATCHES BIN, THEN I WANT THE "VESSEL"
VALUE
TO BE UPDATED IN THE FIELD IN "TEST" CALLED "VessNum".
THE BELOW CODE SHOULD UPDATE THE FIELD, BUT AGAIN, I
DON'T
KNOW HOW TO FIND THE RIGHT FIELD.

Set rs = DBEngine(0)(0).OpenRecordset("Test",
dbOpenDynaset)
With rs
![VessNum] = Vessel
.Update
End With
rs.Close
Set rs = Nothing
End If
End Sub

Thanks in advance for any help.
Note: if there is a better way to do this, please let
me
know.


.

.



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


.
Click to expand...
 
Back
Top