How can a using SQL statement to check a field what it is text for

[Alan48]

Here is the coding i developed. i have the question if a table field (Lot_No)
is a number format. this code is running normally. When i have changed to
text format (Lot_No) in table field. It has an error prompt out. Now i am
guessing the problem is caued by field format. If i would like to change it
to number format and this sql statement still running properly, how can i do?
Thank you

Private Sub Lot_No_AfterUpdate()
Dim db As Database, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * From tlbReceiving WHERE Lot_No = " &
Me![Lot_No] & ";")

With rst
Me![Description] = !Description
Me![Remark] = ![Remark]
.Close
End With
End Sub

 

[Brian]

When referring to text instead of numbers, the text needs to be wrapped in
single quotes. I haven't tested this, but I think your SQL stmt should look
more like this:

Set rst = db.OpenRecordset("SELECT * From tlbReceiving WHERE Lot_No = '" &
Me![Lot_No] & "'";")

That is a single quote before the first double quote and a single quote
between the two double quotes before the semicolon.

Having said that, it looks like you are perhaps looking to the recordset to
populate the Description & Remark boxes on your form. It miight be simpler to
use bound text boxes on your form, go to the record represented by Lot_No
(using RecordSetClone in the AfterUpdate event of a selector box that allows
you to enter or select the Lot_No.. Thus, the boxes would populate
automatically with the correct values without the need to manually code the
SQL.

Post a reply if you need specifics on using the RecordSetClone to move to
the respective record.

 

[Alan48]

Do you mean that i use RecordSetClone will easier and more functionable?
Would you teach me too thx. If i am using this structure, how can i convert
it? Thank you very much.

When referring to text instead of numbers, the text needs to be wrapped in
single quotes. I haven't tested this, but I think your SQL stmt should look
more like this:

Set rst = db.OpenRecordset("SELECT * From tlbReceiving WHERE Lot_No = '" &
Me![Lot_No] & "'";")

That is a single quote before the first double quote and a single quote
between the two double quotes before the semicolon.

Having said that, it looks like you are perhaps looking to the recordset to
populate the Description & Remark boxes on your form. It miight be simpler to
use bound text boxes on your form, go to the record represented by Lot_No
(using RecordSetClone in the AfterUpdate event of a selector box that allows
you to enter or select the Lot_No.. Thus, the boxes would populate
automatically with the correct values without the need to manually code the
SQL.

Post a reply if you need specifics on using the RecordSetClone to move to
the respective record.

Here is the coding i developed. i have the question if a table field (Lot_No)
is a number format. this code is running normally. When i have changed to
text format (Lot_No) in table field. It has an error prompt out. Now i am
guessing the problem is caued by field format. If i would like to change it
to number format and this sql statement still running properly, how can i do?
Thank you

Private Sub Lot_No_AfterUpdate()
Dim db As Database, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * From tlbReceiving WHERE Lot_No = " &
Me![Lot_No] & ";")

With rst
Me![Description] = !Description
Me![Remark] = ![Remark]
.Close
End With
End Sub

 

[Brian]

I will make a short example here using your field names. You may know most of
this already, but I will try to be complete anyway.

Create a form whose RecordSource is: SELECT tlbReceiving .* FROM tlbReceiving

Put three text boxes in the Detail section of the form: Lot_No, Description,
& Remark. Set the control source of each to be the respective field from the
tlbReceiving table

Put one unbound combo box in the form's header: Selector. Set its RowSource
to: SELECT tlbReceiving .Lot_No. Make sure LimitToList is True

Here is the code for Selector_AfterUpdate:

Private Sub Selector_AfterUpdate()
If IsNull([Selector]) Then Exit Sub
Me.RecordsetClone.FindFirst "[Lot_No] = " & Me![Selector]
Me.Bookmark = Me.RecordsetClone.Bookmark
Selector = Null
End Sub

Now, you can drop down the Selector box, which shows you a list of existing
Lot_No. When you type in a Lot_No or select it from the list, it will take
you to the record for that Lot_No.

The Selector is used only to find existing records. You would enter a new
record by using the navigation button at the bottom of the form to go to a
new record and typing the data in the Lot_No, Description, and Remark fields.
You can add more text/combo boxes on your form for each of the other fields
in the tlbReceiving table.

Do you mean that i use RecordSetClone will easier and more functionable?
Would you teach me too thx. If i am using this structure, how can i convert
it? Thank you very much.

When referring to text instead of numbers, the text needs to be wrapped in
single quotes. I haven't tested this, but I think your SQL stmt should look
more like this:

Set rst = db.OpenRecordset("SELECT * From tlbReceiving WHERE Lot_No = '" &
Me![Lot_No] & "'";")

That is a single quote before the first double quote and a single quote
between the two double quotes before the semicolon.

Having said that, it looks like you are perhaps looking to the recordset to
populate the Description & Remark boxes on your form. It miight be simpler to
use bound text boxes on your form, go to the record represented by Lot_No
(using RecordSetClone in the AfterUpdate event of a selector box that allows
you to enter or select the Lot_No.. Thus, the boxes would populate
automatically with the correct values without the need to manually code the
SQL.

Post a reply if you need specifics on using the RecordSetClone to move to
the respective record.

Here is the coding i developed. i have the question if a table field (Lot_No)
is a number format. this code is running normally. When i have changed to
text format (Lot_No) in table field. It has an error prompt out. Now i am
guessing the problem is caued by field format. If i would like to change it
to number format and this sql statement still running properly, how can i do?
Thank you

Private Sub Lot_No_AfterUpdate()
Dim db As Database, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * From tlbReceiving WHERE Lot_No = " &
Me![Lot_No] & ";")

With rst
Me![Description] = !Description
Me![Remark] = ![Remark]
.Close
End With
End Sub