Updating record based on textbox entry

[timmy]

Hello everyone

I posted a similar question a while back in the forms section (https://www.accessforums.net/forms/m...rch-11840.html).



I got a nice reply but still need a bit more assistance. Basically i want a text box that updates what record is shown on the form by user input.

Here is an example:

customer A: aaabcd
Customer B: aaddc

User types: "aa"
it will show whichever record is first

User types: "aaab"
It will show customer A.

A good example of this is the default search that access has on the bottom of each form (i basically want to move this onto the actual form).


This is what i have so far, this code is placed behind the button:

 
Private Sub Searchbutton_Click()
   SQL = "select * from Workorders by Customers " & _
   "where Companyname LIKE '*' & '" & Me.Searchtext & "' & '*'"
 
   Me.Workorders by Customer.Customers = SQL
   Me.Workorders by Customer.Requery
End Sub

The form is called "Workorders by Customer"
Textbox is called "Searchtext"
Button is called "Searchbutton"
Table is called "Customers"
Field i want to search is "Companyname"

Thanks a lot

Edit: Btw, this code gives me an error "Compile error: Variable not defined" and it highlights line 2 ("SQL =")

[pbaldy]

Well, your error is because you didn't declare the variable:

Dim SQL As String

[timmy]

That solved that problem, thanks a lot .

Running into other problems now, not setting the right form names or recordsources in the right places, could do with a hand with that too.

Btw, i figured out that spaces cant be used in vba, so im using "Workorders_by_customer" now.

I thought i got it right from the information adam gave me in the other post... obviously not

[pbaldy]

Well, it's not really accurate to say that spaces can't be used in VBA, but it is certainly better to get rid of them. They're more trouble than they're worth in the long run.

Not clear on what you're other issues are.

[timmy]

Getting "Method or Data Member Not Found" errors. (im guessing it means ive defined form and field names in the wrong places)

Does the code look right to you?

[pbaldy]

What's the code now? What line does it error on? This line:

Me.Workorders by Customer.Customers = SQL

would have to refer to the row source property of the combo or list box.

[timmy]

Private Sub Searchbutton_Click()
Dim SQL As String
   SQL = "select * from Workorders_by_Customers " & _
   "where Companyname LIKE '*' & '" & Me.Searchtext & "' & '*'"
 
   Me.Workorders_by_Customer.CompanyName = SQL
   Me.Workorders_by_Customer.Requery
End Sub

Thats my code at the moment, its not a list or combo box im searching, its a table . The form is called Workorders by Customer and the field im searching is called CompanyName.

Btw i tried changed the last bit to:

Me!CompanyName = SQL
Me.Requery

Using that i get this error:
Run-time error '3163':
The field is too small to accept the amount of data you attempted to add. Try insterting or pasting less data.

I thought i understood the code.... but when i saw the error 3163 ive lost all confidence

[pbaldy]

I didn't pick up on the fact that this was a textbox. You can't just set it to SQL like that. It's trying to put the SQL string in there, not the result (and the SQL doesn't look valid anyway). You either need to open a recordset on your SQL, or switch to a DLookup. Try

SQL = Nz(DLookup("Companyname", "Customers", "Companyname LIKE '*" & Me.Searchtext & "*'"), "")

Me!CompanyName = SQL

[timmy]

Excellent, im finally getting somewhere with this . I tried the DLookup, so now my code looks like this:

Private Sub Searchbutton_Click()
Dim SQL As String
SQL = Nz(DLookup("Companyname", "Customers", "Companyname LIKE '*" & Me.Searchtext & "*'"), "")

Me!CompanyName = SQL
Me.Requery
End Sub

Whats happening now is; its just changing the Companyname field to whatever i type.

Without the Me.Requery, nothing happens, the code is executing tho.

[pbaldy]

What is the requery for? Can you post the db?

[timmy]

uploaded a modified version of the DB. people wouldnt be happy if i left our client base on there .

A lot of stuff doesnt work, like the workorders etc but the search bit is exactly how it is on the real DB.

Thanks for taking a look.

[pbaldy]

What do I have to do to recreate the problem? It appears to be working as I'd expect. If I type in "te" into the box and click the button, Test Company appears. If I type "ex" then Example company appears (I'm having screen refresh issue, but I think it's because I'm remoting to another PC that has 2007 on it). You realize your code is changing the displayed record? I tested with an unbound textbox and it worked without my refresh issue.

[timmy]

very strange.... well heres exactly what im doing and what result im getting:

i have test customer up (test street, customerID 1 etc)
I then type in "ex" in the searchtext and press searchbutton
The Companyname changes to example customer
Address and CustomerID are still that of test customer.

Also if i then go to the Customers table, it has changed the name of customerID 1 to example customer (from test customer).

again, here is the code im using:

 
Private Sub Searchbutton_Click()
Dim SQL As String
SQL = Nz(DLookup("Companyname", "Customers", "Companyname LIKE '*" & Me!Searchtext & "*'"), "")
Me!CompanyName = SQL
End Sub

That part "Me!CompanyName = SQL". Is that not telling it that the companyname must = the result from the line above? And therefore its just changing the companyname to match the record of what the DLookup finds, rather than just displaying that customer.

I think i may have worded my question wrong from the beginning, i wanted to search for a record, not change it (i shouldnt have used the word update). so what i wanted, was to type in "ex" and for it to display example customer, rather than change the record to match it.

[pbaldy]

That's why I asked if you realized that code was changing the record. Have you tried the combo box wizard, choosing the third option, "Find a record..."?

[timmy]

yes ive tried that, i cant really use a combo box as im using ODBC to connect to a MySQL server and a combo box with that many records (around 200) is quite laggy, the keyword search is a solution for that as it wont search everytime you type a letter, but only when you press the search button.