Not In List Question

[g28dman]

Need a little direction on this. I have three tables as such:

tblState
StateID [PK]
StateAbb
StateName
Select (Y or N)

tblCounty
CountyID [PK]
StateID [FK]
CountyCode
CountyName (Y or N)
Select (Y or N)

tblCrop
CropID [PK]
CountyID [FK]
CropCode
CropName

There are over 3000 counties in the US and each county has it's own crops, which could or could not match another county. I placed a Y or N field on state and county tables so that users could select where they primarily do business, and my combo boxes only list what has been filtered - this way users do not need to see anything but what they want.

How do I handle a Not In List if a user wants to add a new county or state for that matter, where they would have to place a checkmark in that box? Or, should I just drop the whole pre-selection idea?

 

[vbaInet]

First thought. If the County is linked to the State, shouldn't the user just select a County and from that you would know what State that County belongs to? Or is the County optional?

Can a user choose more than one County and/or State?

 

[g28dman]

That was my original thought...But I came across several counties where the name was duplicated in another state, and I need additional data to fall in under the crop table that is unique for that county/crop.

Yes multiple states and counties can be chosen.

They state option would only be used to drill down to county level for easier selction of counties - and it can be used to distinguish which county/state looking at combo box

(multiple column County Selection combo)-->(Crop Selection Combo)---->User Imput
CountyName TX
CountyName TX
CountyName OK
CountyName NM

Thanks

 

[vbaInet]

That was my original thought...But I came across several counties where the name was duplicated in another state, and I need additional data to fall in under the crop table that is unique for that county/crop.

This is the problem when you give users the power to add more states and counties. The list of States and Counties should be fairly easy to get and you as the developer add it in manually. In my opinion, that should only be maintained by the DB Admin. If it were something like a list of crops then it would make perfect sense to allow the users add to it (if not in list).

 

[g28dman]

This is the problem when you give users the power to add more states and counties.

I understand not giving users the ability to add more states or counties and I have them all in the system.

To clarify, maybe Not In List was the correct terminology I should have used. For example I have a user on the East coast that does business in just one county in one state - I wanted to give him the ability to update his combo boxes to reflect that one county, while a user in the midwest may have 2 or 3 states doing business in 10 counties each and only want to see those.

Then there's the case where the East coast user may pick up business in an additional county but wont think of updating his combos until he is entering data at which time he'll edit the list to include it.

This was more of an enhancement and if it isn't proper database design I wont do it - just trying to help the user see only what they want.

 

[vbaInet]

Have a look at the attached sample db.

Enter an AccNumber that does not exist and see what happens.

 

[g28dman]

I modified it to reflect what I am trying to do.

 

[vbaInet]

The Not In List event works for items that are not in the combo box values, not the underlying query. If the user didn't select that item then he/she wouldn't be able to see it in the combo box.

You could create a button that could handle this.

1. The user clicks the "Add/Edit" button
2. A form pops-up listing all the available items
3. The user ticks which one he/she wants to add.

 

[g28dman]

Thanks - I'll see what I can come up with.

 

[vbaInet]

Oki-doki. Give us a shout if you get stuck.

 

[g28dman]

Well, I am having a harder time with this and it'll probably be some simple fix, that if I kept going I could have figured out.

This is a stripped Db, frmMain chose county..Not in list open nice little wizard to add. But can't 1) Remove what I had initially typed (say wrong spelling) and get the combo box to update with new filter or 2) I can get the combo to clear, but cant get the record in county form to update in my combo box.

If you try it use only texas counties as they are the only ones with crop info at this point, except first two.

Thanks for helping....

 

[vbaInet]

1) In the pop-up include a text box that will have the County name that was previously entered. Lock the textbox and have a "Edit" Button. Also have a cancel button on that pop-up which will delete the record from the table.

2) Not entirely sure what you mean?

 

[g28dman]

1) In the pop-up include a text box that will have the County name that was previously entered. Lock the textbox and have a "Edit" Button. Also have a cancel button on that pop-up which will delete the record from the table.

Not quite sure I follow you on this. Say a user doesnt spell a county name correctly in county selection, the edit button on the on the pop-up is used to correct it? Why would a user need to check the correct county spelling, then edit(if need be) when it's already there?

2) I can get what was previously entered to clear out [Not in List], but can not get the updated value back in from pop up which was selected using a tick box.

 

[vbaInet]

It's not there to be corrected, it's there to be editted. A user may decide that he/she doesn't want to use that description and by the text box being there he/she has the opportunity to edit it. Once that pop-up is closed the user has no other opportunity of changing it so you might as well give them a second chance.

 

[g28dman]

It's not there to be corrected, it's there to be editted. A user may decide that he/she doesn't want to use that description and by the text box being there he/she has the opportunity to edit it.

I still do not understand I already have all the counties entered, the user shouldnt need to edit the description that they entered.

I am saying if the county name was incorrectly entered on the mainform or new county was being added the pop up box shows a listing of counties by state which the user will select what county needs to be added - once that tick is placed by the county, I can not get my combo box on my main form to requery to reflect the addition.

 

[vbaInet]

Ah alright. So you basically want to requery the combo box. What happens when you do a Requery?

 

[g28dman]

The popup contains a form generated from all counties and includes a tickbox that filters only those counties that they want to work with.

I would like once a user selects a new county to add to their personal filter, the combo box on the main form is to be requeried to include the added county.

Incidently I have tried on the ok button of the pop up:

Forms!frmMain!Combo0.Requery

and

Forms![frmMain].Controls![Combo0].Requery

and this removed what was entered but not in list, but still couldnt get combo to requery

Forms![frmMain].Controls![Combo0].Value = Null

kept getting error Runtime error 2118
You must save the current field before yuo run the Requery action and highlights the requery codes above.

I do have Save code before.

 

[vbaInet]

If you look in the sample db I attached in one of my posts you will find the sequence of events that need to take place to get it to work. It would be on the CLOSE EVENT of the pop-up form. Just replicate what's in there (except the IF ... OpenArgs bit) and put it in the click event of your OK button.

 

[g28dman]

Bada-Bing / Bada-Boom, there she is!! I wanted to add a Close Me on pop-up but it kept closing the main form, so I made a seperate close button which enables once code is ran.

Thanks alot for your continued help.

 

[vbaInet]

You can specify which form you want to close on the Close method:

docmd.Close acForm, "Name_of_Form"

You're welcome. Glad that's sorted.