Need help about subForm

[pido]

Hi im newbie in access programing for form, and I have a problem in my Form.
Basicaly i have Order Form with a subForm which is Order details. Record source for my subForm is a qry_orderDetails that i made, TextBoxes are 'PK_partNumber,partName,and partPrice'
What i want is when i put textBox 'PK_partNumber' lets take '12345' and i want field 'partName' automatically search in my 'tbl_sparePart' table and put its Name in the 'partName' field
Should i make a code using Visual Basic Code and make my own query to search the textBox 'PK_partNumber'?? bcuz i tried it but it give me an error saying "#Name"

this is my code:

Private Sub partNumber_Change()

Dim txtSearchString As Variant
Dim strSQL As String

txtSearchString = Me!partNumber.Text

    strSQL = "SELECT tbl_sparepart.PK_partName FROM tbl_sparePart"
    strSQL = strSQL & "WHERE ((tbl_sparepart.PK_partNumber) = '" & txtSearchString & "*') "

Me!partName.RecordSource = strSQL
Me!partName.Requery

End Sub

anyone can help me?? i would be appreciate it.. Thnx in advanced

 

[edtab]

You should be using a combobox instead of a text box for your 'PK_partNumber' .

When you drop down your combobox, you should be able to capture the
'partname' using the following code in the afterupdate:

Me.partname = me.comboboxname.column(1)

Make sure your combobox has at least two columns:

Column 1 Your PartNumber (index 0)
Column 2 Your PartName (index 1)

PartName has an index number of 1 and PartNumber has an index of 0.
That is why the code above reads .column(1) instead of .column(2)

Hope this helps you.

 

[godofhell]

Your code appears to be correct except that you forgot to include a space between the FROM tbl_sparePart" and "WHERE. Write it as FROM tbl_sparePart " or " WHERE. The way it is now would read FROM tbl_sparePartWHERE.

Private Sub partNumber_Change()

Dim txtSearchString As Variant
Dim strSQL As String

txtSearchString = Me!partNumber.Text

strSQL = "SELECT tbl_sparepart.PK_partName FROM tbl_sparePart"
strSQL = strSQL & "WHERE ((tbl_sparepart.PK_partNumber) = '" & txtSearchString & "*') "

Me!partName.RecordSource = strSQL
Me!partName.Requery

End Sub

 

[pido]

Sorry for the late reply...

edtab, i got a picture if you said i should put a combobox for my PK_partNumber, but i have a lot of partNumber like more than thousand.. i can't imagine how combobox handle it. I just want whe i typed my part number, then the part name will show up in textbox 'partName'

godofhell, i did put the space between FROM tbl_.." and "WHERE.. but it still wont work.

OK First of all i have 3 tables, "tbl_orders" "tbl_orderDetails" and "tbl_sparePart". and in my Form Orders i put "tbl_orders" as my RecordSource and for SubForm Orders i put "tbl_orderDetails" as my RecordSource.
question is what i wrote above, when i typed partNumber then in partName textBox will show up the part name. i put the code in AfterUpDate(), can i do this in textBox or i should really make comboBox for my partName??

 

[edtab]

Annent to my earlier comment:

You do not have to drop down your combobox to capture the partname.
With an autocomplete combobox, all you have to do is type in the part number and if there is a match, it will automatically go to that record and as soon as you hit enter, it should then display the partname in the designated column.

With a few thousand records, I am not sure how fast this will happen, but I suspect that the response time should be acceptable.

It is worth a try if your code does not work. I know my method works 'cause I've used it before.