Search unbound textbox and populate subform

[naym]

Hi All,

I have a small problem.

I have a form with a textbox which when users enter a unique number(barcode) I want to run a query which pics up the barcode number, checks against the product id and fills the subform with the name of the product and price.

the basic details of the product table is like:
product id (Autonumber)
prdoduct name (text)
price
barcodeId (number)

the subform where i want the result to go is the order details
fields product name and price.

Im trying to figure out how to do this but my mind keeps going blank
its been a long while since i used ms access.

Any help on this would be appreciated.

Kind Regards

 

[mdlueck]

I assume you have a button or similar control which is going to perform the search, correct?

If so, perhaps borrow logic from a form I have which searches for parts to attach to a product:

Private Sub btnSearch_Click()
  On Error GoTo Err_btnSearch_Click

  Dim strFindText As String

  'Put the mouse hourglass pointer
  Call uiutils_SetMouseHourglass

  'Find out if the Search Field has a value in it, else we outtahere!
  strFindText = uiutils_ReadFormTextBox(Me.fldpartnumbersearch, vbNullString)
  If strFindText = vbNullString Then
    Me.fldpartnumbersearch.BackColor = vbRed
    GoTo Exit_btnSearch_Click
  Else
    Me.fldpartnumbersearch.BackColor = vbWhite
  End If

  'Make sure that the string is within the limit of the Stored Procedure Parameter length
  If Len(strFindText) > 25 Then
    Me.fldpartnumbersearch.BackColor = vbRed
    Call errorhandler_MsgBox("Form: Form_productspartsassociateadd, Subroutine: btnSearch_Click()" & errorhandler_PartsSearchStringTooLong())
    GoTo Exit_btnSearch_Click
  End If

  'Load the search string value into the Validation object
  ObjUIValidationProductPartLinkTbl.searchstring = strFindText

  'call the Validation object to perform the query
  If ObjUIValidationProductPartLinkTbl.Search() = True Then
    'Refresh the Form by re-firing the query the Form is based on
    Me.Requery
  End If

Exit_btnSearch_Click:
  'Clear the mouse hourglass pointer
  Call uiutils_SetMouseDefault

  Exit Sub

Err_btnSearch_Click:
  Call errorhandler_MsgBox("Form: Form_productspartsassociateadd, Subroutine: btnSearch_Click()")
  Resume Exit_btnSearch_Click

End Sub

 

[naym]

Hi There,

Thank you for you reply,

Yes I was thinking of using a command button or attach the code to the afterupdate event on the textbox.

Im sorry but i dont think your code will do what i need to do.
I was thinking of the lines of having a vba sql select statement which
in my head is something like:

Select * from products where barcode id = me.textbarcodesearch.value

but what i cant work out then is when when the statement brings the result how to i select the product name from the query and insert that into the product field of the subform and price from the products table and insert that into the amount field of the the subform.

basicaly im trying to get the product details by searching in the barcode field and insert the name and price of the product into the order details subform .

the subform is not connected to the products so maybe just a simple copy and paste would do.

I hope that maybe makes things a little clear of what im trying to do. as i think my explaining is not the best.

Kind Regards

 

[mdlueck]

I was thinking of the lines of having a vba sql select statement which
in my head is something like:

Select * from products where barcode id = me.textbarcodesearch.value

Which is what my code does inside those class objects.

Since you want the result set to end up in an Access table, then I would suggest putting the query in a DAO.QueryDef object which those may interact directly with Access Tables.

but what i cant work out then is when when the statement brings the result how to i select the product name from the query and insert that into the product field of the subform and price from the products table and insert that into the amount field of the the subform.

basicaly im trying to get the product details by searching in the barcode field and insert the name and price of the product into the order details subform .

So in the Search button code, grab the selected record value from the main form (Per my code that would be Me.fldpartnumbersearch) if that is a valid thing to look up, then feed that into the SQL statement, build/update a DAO.QueryDef, Execute said DAO.QueryDef, which populates the table that the subform is displaying.

Better?

 

[naym]

Hi Michael

Thanks for the reply,

Yes i kinda get you know, ive never used this type of class or programming before so sorry if i didnt understand you code.

do you my any chance have a working example of this so i can get a idea of how it works and how i may be able to replicate it into my application?

Kind Regards

 

[mdlueck]

do you my any chance have a working example of this so i can get a idea of how it works

That is the closest example code I have available... which uses VBA Classes, SQL Server Stored Procedures, and related technologies.

I do have a post of how to use DAO.QueryDef objects, but it is a bit more complicated than you need as it sends a Pass-Through query to the SQL BE DB and downloads records into a FE table. You would only need the FE table query in your case.

http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

 

[naym]

Hi Michael,

Thanks again for the reply I will take a look at this tonight and see what i can do or make of it. by the way is this based on sql server?? am just using ms access no sql server just sql statement. lol

 

[mdlueck]

by the way is this based on sql server??

Yes, my sample code is for an Access front end to a SQL back end. That is one added complexity that will be eliminated the way you are going to implement.