I need an example of a VB script.

[travismp]

I have a form with a subform. When my user types in a bar code in the search form the subform will then show the result. My user then has to go down into the subform and check a box [Rcvd] and then add todays date into [Rcvd_Date]. Then they go back to the main form and search again.

I want to add a button to the main form so when they search they see the record, then click the button. Once the button is clicked the script will check the box, enter todays date, then go back to the search field ready to start again.

Does anyone have a similar project I can use as an example when building this? thank you!

 

[jal]

I have a form with a subform. When my user types in a bar code in the search form the subform will then show the result. My user then has to go down into the subform and check a box [Rcvd] and then add todays date into [Rcvd_Date]. Then they go back to the main form and search again.

I want to add a button to the main form so when they search they see the record, then click the button. Once the button is clicked the script will check the box, enter todays date, then go back to the search field ready to start again.

Does anyone have a similar project I can use as an example when building this? thank you!

Not sure where you're having a problem.
Have a texbox on the form called txtkKeyword where the user can type in his search phrase.

When you create the subform, you can base it on a query related to txtKeyword (I'm going to assume the asterisk is the wildcard. If that doesn't work, try the percent sign).

SELECT * FROM Products WHERE ProductName LIKE "*" & Forms!Form1!txtKeyword & "*"

Save it as qryProducts.

Drop a command button (btnSearch) on the form (cancel the wizards) and open the OnClick event.


Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs("qryProducts")
'The next line is optional -use it if you want to modify the query
qry.SQL = "SELECT..."
Me.subform1.SourceObject = "Query.qryProducts"
Me.subform1.Requery

One problem is that his keyword might return more than one product. Let's suppose it return five products now shown in the subform (DataSheet view?). If he now uses his mouse to select the third product, I'm not sure how to detect this. (I've generally used listboxes rather than subforms). Maybe I'll give this a try myself so I can learn how to do it.

 

[jal]

Just occurred to me - since Access views the subform1 as a collection of textboxes, it assigns a name to each textbox (by default the column names of the table).

Therefore if you want to know which product the user selected, use the value in the textbox called ProductID - this could be either ProductID.Value or ProductID.Text (not sure which one as I haven't tested). Then do your update.

DoCmd.RunSQL "UPDATE Products SET Recvd = TRUE, DateRecv= Date() WHERE ProductID = " & ProductID.Value (or maybe ProductID.Text).

 

[jal]

Well, I tried it briefly. I guess, instead of ProductID.Value, it would be:

subForm1.Controls("ProductID")