check entry is new versus lst box

[chrisjames25]

Hi I'm embarking on my first database and hoping to learn as i go.

My first query is on forms and new entries.

I have a form for inputting Crisp Brands. On the form is a Txt_box for entering the crisp brand an unbound txt box for renering the text box to ensure spelling is correct and a lst_box listing all existing crisp brands in the sytem.

As i type into the first txtbox i would like to search the listbox and highlight any matching names and then on trying to exit the first txt box have a message box pop up saying brand of crisp already exists.

I know how to achieve this in excel but access is all new to me. Any suggestions would be great

 

[pr2-eugin]

Have you tried to look into John Big Booty's - Dynamically search multiple fields, that should give you a start.

 

[chrisjames25]

Hi this is kind of what i'm looking for however i didnt want to minimise the lstbox values down. Say i have the following lists: Prawn Cocktail; Ready Salted; Cheese and onion; Salt and Vinegar. As i type salt in the textbox i would wnat all 4 items to remain in the lst box but it to have selected Salt and vinegar as i have typed salt.

Is this possible

 

[pr2-eugin]

As I said the code is only a start, you could always modify the code to make it suit your needs.

Also, would you not be better of using a ComboBox over a ListBox?

 

[CJ_London]

you would need to set your listbox multi select property to simple or extended, then use vba code to set selected=true by parsing through the entries looking for the match

see this link

https://msdn.microsoft.com/en-us/library/office/ff835338.aspx

I'm assuming the example does not relate to real life since it will soon become unmanageable as the list is extended and you run out of screen room.

Access is not excel and has better ways of avoiding duplicates - for example simply setting an index with 'duplicates not allowed' on the field in your table

 

[chrisjames25]

WIth the unique variable how do i stop it using its generic warning message and allow me to use my own mesage?

Many thnaks for all your help so far.

 

[CJ_London]

that depends on how your form works, probably the easiest is to trap it before the record is updated - suggest your textbox beforeupdate event put something like (change names to suit)

if dcount("*","tblBrands","BrandName='" & mytextbox & "'")<>0 then
    msgbox "This name already exists, please try another one"
    cancel=true
end if

 

[chrisjames25]

cheers i think that will do the trick nicely. one follow up. i assume i wud use a query in the code instead of table if for example i have a column for brand and a column for flavour so i cud have brand walkers flavour ready salted brand branigans flavour ready salted but cudnt then add brand walkers flavour ready salted so i run query to filter down to a particular brand then ensure it is unique to that brand hope that makes sense

 

[CJ_London]

i assume i wud use a query in the code instead of table

yes

filter down to a particular brand then ensure it is unique to that brand

you would extend the dcount criteria

"BrandName='" & mytextbox & "' AND Flavor='" & myFlavourTextbox & "'"

 

[chrisjames25]

Perfect. Works great.

Many thanks for all your help