If text string matches while typing then need related text to auto appear in combobox (1 Viewer)

[moto485]

Hi I have attached an example, I new to this and just can't figure out how to do this.

If you have a look at the Form, when typing in a Item if it matches the ProductList table say we type Pedals I want Monza to be returned in the Supplier field. If nothing is returned then we can select a supplier manually.

I would like to be able to add extra products to the suppliers and change them as needed. Might have 20+ suppliers and many products.

I am struggling to find some answers so if someone could give me a hand that would be great.

Thanks

 

[Alansidman]

You are having issues primarily because your database is not normalized. It set up to look and act like a spreadsheet and not a relational database.

You should read this on design of databases

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Now, here is how I would set it up to begin with. Not knowing what else you wish to do, this would be a starting point.

tblVendors
VendorId (PrimaryKey)
VendorName
OtherVendorFields

tblProducts
ProductID (PrimaryKey)
Product
VendorID (ForeignKey)

Once this is set up, you can use combo boxes in your forms and you can set up queries for information extraction.

 

[moto485]

Hi thanks for the reply I kind of just did quick example but thanks for link will read up. But I am still wondering about the steps needed to make this happen. I am not sure how I can use SQL to get the result? won't i need expressions or the like? I am happy to do some reading but I am not sure how to go about it. Thanks

 

[r.harrison]

Hi Moto,

I've looked at your DB, as Alanisdman said, you need to normalise the tables

Check the attachment for a quick idea.

 

[vbaInet]

Yep, you will use LIKE '*' & [Forms]![FormName]![TextboxName] & '*' and Requery the form. But I would advise that do it (i.e. Requery) on the After Update event of the textbox and not as the user types.

Or you can use the Filter and Filter On properties of the form:

Me.Filter = "[FieldName] LIKE '*" & Me.[[COLOR=red]TextboxName[/COLOR]] & "*'"
Me.FilterOn = True

But like Alan mentioned, you need to normalize.

Happy developing!

Edit: Posted a bit late, didn't even see r.harrison's post.

 

[r.harrison]

If you really want it to update as you type then you'll need to look at the Keydown, Keypress, KeyUp events.

I have done this before just because it looks good. But as vbaInet says, much easier on the AfterUpdate.

 

[vbaInet]

If you really want it to update as you type then you'll need to look at the Keydown, Keypress, KeyUp events.

Actually on the On Change event.

I have done this before just because it looks good. But as vbaInet says, much easier on the AfterUpdate.

I'm sure it does I would only use it on a small dataset or on a powerful machine. It's easy on the db engine.

 

[r.harrison]

I thought the OnChange event only fires once i.e on the first keypress?

 

[vbaInet]

I thought the OnChange event only fires once i.e on the first keypress?

Na, it's continuous. That is, every key stroke.