Displaying results of query in form text boxs

[mgillespie21234]

OK so here's this situation: I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function (I think... need some help) to display the results onto a form called EnterInventory. Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form. PLEASE HELP!! I will be happy to clarify if this is confusing.

 

[lespritdelescalier]

A simple way to do this would be to use the AfterUpdate event for the textbox to load the EnterInventory form, and pass the barcode value to the form so it can be used in your query. You can set the RecordSource property of the EnterInventory form to your FindProduct query, and use the value you passed as an input parameter for the query (or you can point the query at the value no the Scan_Barcode form) to execute the query. Link the fields on the EnterInventory form to the fields in your query, and it should load the data for you.

Hopefully this makes sense - if not, I can show you some sample code that I might use to accomplish this.

 

[mgillespie21234]

I will give this a try on Monday. I'm sure I'll have some questions.... Thanks for your help!

 

[mgillespie21234]

A simple way to do this would be to use the AfterUpdate event for the textbox to load the EnterInventory form, and pass the barcode value to the form so it can be used in your query. You can set the RecordSource property of the EnterInventory form to your FindProduct query, and use the value you passed as an input parameter for the query (or you can point the query at the value no the Scan_Barcode form) to execute the query. Link the fields on the EnterInventory form to the fields in your query, and it should load the data for you.

Hopefully this makes sense - if not, I can show you some sample code that I might use to accomplish this.

----
Im a little confused... Could you help me with some sample code?

 

[lespritdelescalier]

I just got your private message.. but since I don't have a high enough post count, I can't respond to you. I'm just going to grab a bowl of cereal, and then I'll write you a proper response.

Thanks!

 

[lespritdelescalier]

Hopefully you're able to get the data from the bar code scanner to access? I've never done that before. There are some tutorials if you google "using a barcode scanner with access".

After you are able to get the input from the scanner, you'll need to load the EnterInventory form using the code below, either by creating a button on your Scan_Barcode form, or possibly by using the AfterUpdate event on the text box on the Scan_Barcode form.

DoCmd.OpenForm "EnterInventory", acNormal

Open your query, and make sure that the ID for the item you want to load is set to something like:

[Forms]![Scan_Barcode]![YOUR TEXTBOX NAME]

In the Form_Load event for EnterInventory, you'll want to call up your query:

Dim db as Database
Set db = currentDb

Dim qDef as QueryDef
Set qDef = db.QueryDefs("ENTER YOUR QUERY NAME HERE")

Dim rs as Recordset
Set rs = qDef.OpenRecordset()

(You may actually want to have this in another subroutine in case you don't want to close the form every time after you're done.)

Hopefully this only returns one record, otherwise it's going to get a bit messy. Each product should have it's own unique ID linked to the barcode.

After that, you'll just want to assign each field from your query to the respective textbox on your form, by doing something like this:

Me!TEXTBOX_NAME1 = rs!FIELDNAME1
Me!TEXTBOX_NAME2 = rs!FIELDNAME2
Me!TEXTBOX_NAME3 = rs!FIELDNAME3

And then make sure to close your recordset at the end, and refresh the form:

rs.Close
Me.Refresh

Give that a try and see if it gets you where you want to be.

 

[mgillespie21234]

Thanks I will give this a try

 

[mgillespie21234]

Hopefully you're able to get the data from the bar code scanner to access? I've never done that before. There are some tutorials if you google "using a barcode scanner with access".

After you are able to get the input from the scanner, you'll need to load the EnterInventory form using the code below, either by creating a button on your Scan_Barcode form, or possibly by using the AfterUpdate event on the text box on the Scan_Barcode form.

DoCmd.OpenForm "EnterInventory", acNormal

Open your query, and make sure that the ID for the item you want to load is set to something like:

[Forms]![Scan_Barcode]![YOUR TEXTBOX NAME]

In the Form_Load event for EnterInventory, you'll want to call up your query:

Dim db as Database
Set db = currentDb

Dim qDef as QueryDef
Set qDef = db.QueryDefs("ENTER YOUR QUERY NAME HERE")

Dim rs as Recordset
Set rs = qDef.OpenRecordset()

(You may actually want to have this in another subroutine in case you don't want to close the form every time after you're done.)

Hopefully this only returns one record, otherwise it's going to get a bit messy. Each product should have it's own unique ID linked to the barcode.

After that, you'll just want to assign each field from your query to the respective textbox on your form, by doing something like this:

Me!TEXTBOX_NAME1 = rs!FIELDNAME1
Me!TEXTBOX_NAME2 = rs!FIELDNAME2
Me!TEXTBOX_NAME3 = rs!FIELDNAME3

And then make sure to close your recordset at the end, and refresh the form:

rs.Close
Me.Refresh

Give that a try and see if it gets you where you want to be.

Hey Les,

I tried this today and message around for a while but kept getting this compile error.

When you say "(You may actually want to have this in another subroutine in case you don't want to close the form every time after you're done.)"

do i put the:

me!textbox13 = rs!make

In a:

Sub ForceSubRoutine

End Sub

?

 

[mgillespie21234]

Figure out first error. Now I'm getting a Run Time 3265 error Item not found in this collection....