Combobox how to reset if without required value (1 Viewer)

hugomadail

Registered User.
Local time
Today, 07:00
Joined
Aug 31, 2018
Messages
25
Hello, I have a issue with a combobox in my BD, Ill explain below (and I hope to explain it well):

- I have a form "frmproducts" from "tblproducts" - fields ProductID, ProductName, Store.
- field Store is a combobox that shows the contents of tblStore (it shows StoreName but links with StoreID)

I have a button next to combobox that works like: If combobox Store is null, open "frmaddstore" with newrecord. If combobox has Store inserted, it open "frmaddstore" and goes to current record for edit purposes.

All worked fine, but Store combobox could be left empty so I changed Store to a required field for not having products unrelated with stores.

Now it doesnt let me go out of frmproducts or go to new record without assigning a store on Store combobox, shows an error message, just as wanted.

BUT this error message also shows on Store combobox after I type something and doesnt let me do anything before assigning a Store on combobox.
My idea is to seach in combobox for the Store, and I want to type text to speed seach (ex: type mech on combobox to find mechatech faster) BUT if I dont find the store (not exist yet on BD), even if I empty all text on combobox it doesnt let me use the button to add a new store and shows the error message "must have value in field Store".

I'm like Yeah I know that dear error but I need to add store first. If I press ESC after typing on combobox it kinda resets and let me do it, but I would like it to reset without pressing esc.

How can I solve this problem ? I hope you can help :)
 

Insane_ai

Not Really an A.I.
Local time
Today, 03:00
Joined
Mar 20, 2009
Messages
264
There are two simple ways I can think of to address this.
1. Add a default record into tblStore that can be used as a trigger to open your other form if that one is selected. (This is not my preferred method as it adds dirty data)


2. Manually populate the Combobox with a default value then load the rest of the list with a recordset something like this: (untested pseudo-code)

Code:
Dim DB as DAO.Database
Dim RS as DAO.Recordset
Dim strSQL as String
	'Get the list of stores in your table and put it into a recordset
	strSQL = "Select StoreID, StoreName FROM tblStore"
	Set DB = CurrentDB
	Set RS = DB.OpenRecordset(strSQL)
	
	'Fill the list in the Combobox adding a manual "default value" in the list so it is not blank	
	With me.combobox
		.AddItem "0;AddStore"		'default value to trigger opening your other form assuming your tblStore does not already have a StoreID 0
		'Cycle through the recordset to add the rest of the values from your source table
		With RS
			.MoveFirst		'Go to the beginning of the records
			Do Until .EOF		Do until the End Of File
				.AddItem
				.MoveNext
			Loop
		End With
	EnD with
 

hugomadail

Registered User.
Local time
Today, 07:00
Joined
Aug 31, 2018
Messages
25
1. Add a default record into tblStore that can be used as a trigger to open your other form if that one is selected. (This is not my preferred method as it adds dirty data)

I'm trying to avoid dirty data, so I'll leave this for the last


2. Manually populate the Combobox with a default value then load the rest of the list with a recordset something like this: (untested pseudo-code)

Noob question, I want to try this code, but I missed the place to use it, Store combobox after update or combobox source maybe ? Might not me the "frmaddstore" button LOL
 

hugomadail

Registered User.
Local time
Today, 07:00
Joined
Aug 31, 2018
Messages
25
Went for a quick break and a brain lightbulb just turned on for another idea (not sure if possible):

All my problem is because I wanted Store to be a required field and turned that option ON in tblProduct, that makes the error appear when combobox Store is empty on frmProducts.

MAYBE is it possible that, with "required field" option disabled for field Store, the frmProducts does not let me leave or change record with Store combobox empty ? I mean that would make validation not be on combobox but on the form ? that would allow me to use button first and validade after maybe.
 

Insane_ai

Not Really an A.I.
Local time
Today, 03:00
Joined
Mar 20, 2009
Messages
264
If you want to use method #2, I would put the code into the Form Load event. This will populate the combo box for you.

Reading a bit deeper into your question, I think you may be better served with an unbound form. This requires a bit of extra work but allows you to validate your data before putting it into the table. The short version is all controls will be unbound, their values would be captured in code that would eventually be fed into a SQL statement to insert the data you need.


unfortunately as I was typing this response my day went from waiting on feedback to flooded so I won't be able to provide the process on that right now. I will get back as soon as I can if nobody else helps you along with that piece.
 

Insane_ai

Not Really an A.I.
Local time
Today, 03:00
Joined
Mar 20, 2009
Messages
264
MAYBE is it possible that, with "required field" option disabled for field Store, the frmProducts does not let me leave or change record with Store combobox empty ? I mean that would make validation not be on combobox but on the form ? that would allow me to use button first and validade after maybe.

That would be the easy way, my comment above with the unbound form is the long way around the issue but writing some validation code on the combo box would be simpler.
 

hugomadail

Registered User.
Local time
Today, 07:00
Joined
Aug 31, 2018
Messages
25
Insane_ai, I hope you read this before texting the "unbound form" solution LOL.

I found a solution to cheat the "required field" option and remove the error, Basically disabled required field for Store and added a button to leave frmProducts (also disabled the close button on form), then for events:

Private Sub btnLeave_Click()

If IsNull(cbStore) Then
MsgBox "You cant exit without filling the required fields!", vbOKOnly, "Error"

Else
DoCmd.Close
End If

End Sub

I'm a non programmer, just a auto-learning person and I found this simple solution, even if it looks cheat. But it works :)

Thanks for spending some time trying to help (might be some, since you did a code on method 2).

Maybe well "chat" again someday, see ya another time :)
 

Insane_ai

Not Really an A.I.
Local time
Today, 03:00
Joined
Mar 20, 2009
Messages
264
I'm a non programmer, just a auto-learning person and I found this simple solution, even if it looks cheat. But it works

You did what I would have chosen as the easy answer but I was trying to answer your question under the presumption that you were stuck with the required flag on that field.

With Code I don't think there is cheating, only what works and what doesn't.
 

moke123

AWF VIP
Local time
Today, 03:00
Joined
Jan 11, 2013
Messages
3,852
I want to type text to speed seach (ex: type mech on combobox to find mechatech faster) BUT if I dont find the store (not exist yet on BD), even if I empty all text on combobox it doesnt let me use the button to add a new store and shows the error message "must have value in field Store".

Are you sure you don't want the Not in list event to fire and handle it that way?
 

hugomadail

Registered User.
Local time
Today, 07:00
Joined
Aug 31, 2018
Messages
25
moke123 that could help too, but I have problem solved another way.

What I'm looking now is some code to reset combobox - because I entered some letters to find Store then I delete them when no record found but combobox is different than it was untouched (when new Store added with other form, I set focus on that combobox and suposely it would place new store name on it but it doesnt), not sure how to explain better, does this make sense?

#1 combobox untouched and add new Store in other form, it then auto populate/insert combobox with new store name.

#2 combobox if was seached through records and then add new Store in other form, it DOES NOT populate combobox with new store name.

weird..
 

moke123

AWF VIP
Local time
Today, 03:00
Joined
Jan 11, 2013
Messages
3,852
I'm a little confused trying to follow the thread.
What I think your saying is...


  • You have a combobox which is bound to a store field and gets its rowsource from a table of Stores.
  • You want to find as you type for a store in the combo box.
  • If the Store is there then you select it and your good.
  • If the store is not in the list of stores then you want to enter the store into the store table and have it appear in the combobox as a choice.
  • You want a selection of a store to be required before saving the record

If i'm correct then you probably want to use the not in list event of the combo box.

heres a couple links. If you google it you'll find plenty of examples.

https://www.fontstuff.com/access/acctut20.htm
https://www.devhut.net/2010/06/12/ms-access-vba-not-in-list-event/
https://www.youtube.com/watch?v=G5H7yx-3Hbw

as far as requiring a field you can do that in the forms before update event.
 

moke123

AWF VIP
Local time
Today, 03:00
Joined
Jan 11, 2013
Messages
3,852
heres an example
 

Attachments

  • NIList.accdb
    508 KB · Views: 60

hugomadail

Registered User.
Local time
Today, 07:00
Joined
Aug 31, 2018
Messages
25
moke123
What I think your saying is...

Yup you got it right, that was what I wanted.

moke123
heres an example

Nice, this attached database helped me understand what I was missing, because in that example DB the combobox that opens a new form to add a record was what I wanted.

Problem is solved now!!!
Insane_ai, moke123 thanks for your help! :)
 

Users who are viewing this thread

Top Bottom