How can I use a combobox to change records?

[nonturbo]

I'd like to disable the built-in navigation controls on a form and use a combobox / drop-down box to change records. The form is a subform just for addresses, and the combobox currently selects address type, ie: home, business, other.

Right now, changing the combobox value changes the address type for the current address. I'd like it to change the record itself to the business or home or whatever is selected, so i can disable the nav controls on the form. I know it's the on Change() Event I need to code, but where do I start? Do I write some SQL queries, or is there other ways? Please give me some pointers to get started. Thanks in advance!

 

[WildHare]

It appears you're confusing CHANGING a value and POSITIONING to a new/different record.

If a combo box is BOUND to a field, making a change in the displayed values on the combo will indeed CHANGE the field in the underlying table.

You need to build an UNBOUND combo box that will be used for NAVIGATIONAL purposes. There's a c/b wizard that guides you through this if you select the c/b tool on the toolbar. It's choice #3, "Find a record on my form based on a value in my combo box"... fairly straightforwardish, but give us a shout if you need more. "Remember, you're unique - just like everyone else"
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[nonturbo]

Sorry, but I've yet to master all of the terminology, but it seems I properly explained what I wanted. I also appreaciate your reply, but that wizard only goes about 25% of the way in completing what I need to do.

Let me give a little bit more info...

The form I'm looking to add this to is a subform on another form. The parent form selects the account and the subform will allow you to have multiple addresses for each account. The address type is an Integer (that's the field I want displayed in the combobox) that corresponds to an AddressType table, IE: 1 - Home, 2 - Business, 3 - Other, etc.

As of now, the combobox that the wizard created gives me a very long list of every address type in integer form of every account. I now need to change the 1s and 2s to Home/Business and filter so we only see the ones that are for that account. I'm stumped. Please help!

Thanks again!

 

[nonturbo]

I need to filter the records by AccountNbr = Me.AccountNbr, but apparently that's not the right syntax.

I changed the query for the row source of the combobox that the Wizard created to:

SELECT AddressDataSorted.[Address ID]
FROM AddressDataSorted
WHERE (((AddressDataSorted.AccountNbr)=Me.AccountNbr));

..But this asks me what the Me.AccountNbr is. If I manually enter the account number, then it gives me the right list. But the list is the numerical list, not synced with the AddressTypes table.

..Is what I'm trying to do even possible!? Arrrgh!

 

[WildHare]

OK, I think I know what you want now - you want a combo box to display HOME, BUSINESS and OTHER, and when the user selects one of those guys, display the HOME address or the BUSINESS address or the OTHER address ???

IF you have multiple addresses for each account, but your subform is only displaying the "desired" adddress type, then you need to filter the data shown on the subform. You might try something like this:

Sub cboPickAddressType_AfterUpdate()
me!Formname!SubformControl.Form.Filter = "AddressType=" & me.cboPickAddressType
Me!FormName!SubformContol.Form.FilterOn = TRUE
Me!FormName!SubformControl.Form.Refresh
.....
End Sub
[code]
You'll need to plug in your appropriate form names and all. I'm not convinced that showing this on a subform is necessarily the best way, but you might have your reasons.

Your combo box will have two columns - the text and the number. You can set the width of the number column to zero so it's not visible. But it should be the BOUND column.

RowSourceTYPE: ValueList
Rowsource:  Home, 1, Business, 2, Other , 3
Number columns : 2
Bound Column : 2
Column Widths:  1.5;0
 "Remember, you're unique - just like everyone else"
You're invited to visit another free Access forum:
[URL unfurl="true"]www.forumco.com/jmhare[/URL]
or my site, [URL unfurl="true"]www.jmhare.com[/URL]

 

[raskew]

You might consider using a tab-control to deal with your information. I do this routinely to track employees. The main form is bound to qryStaff, with an Option Group (Name, SSN) and a combo box in the form's header to allow the user to search for and move to a particular record.

The first page of the tab control lists the non-changing items about an individual (e.g., Name, SSN, Sex, Race, DOB), while other pages contain sub-forms linked to the main form via SSN (the unique identifier). Among these subforms are fsubAddresses, constructed as you describe, and fsubContacts (again connected via SSN and listing Home Phone, Home Phone #2, Work Phone, FAX, email, etc.)

If I can ever get my DAO 3.51 problem resolved (see VBA forum) I'd be happy to provide you with a sample application (I could provide it now, but the problem is that it can't be tested since my system is not recognizing things such as CurrentDB.)

 

[nonturbo]

JimAtTheFAA - Thanks for your help so far, but I don't understand your code:

me!Formname!SubformControl.Form.Filter = "AddressType=" & me.cboPickAddressType
Me!FormName!SubformContol.Form.FilterOn = TRUE
Me!FormName!SubformControl.Form.Refresh

From what I gather, I just add these lines to the AfterUpdate event of the Combobox control, above any other code that might be there.. But I have a few little questions: Is formname the parent or the subform? What's SubformControl, the combobox control name? Or isn't cboPickAddressType the combobox?

Raskew - Thanks for your suggestions too. The parent form is infact a tab control as you describe, with a tab labeled "Address Information". On that page is a header and below that the subform. Throughout the entire Application/DB, I've removed 99% of the built-in Access navigation controls because they confused the users. Don't ask! To make a long story short, I figure that having a combobox at the top of the subform that contains the list of available address-types would solve the problem of users creating 15 home addresses and screwing up the queries for labels and other reports. If they select "Home Address" then they'll have that record, "Business" will go to the work address. I'll obviously need to have some error handling involved so that when the query fails to find a record (Address) to create a new record. If you have a good way of doing this, please let me know.


Thanks everyone
-NT

 

[WildHare]

But I have a few little questions: Is formname the parent or the subform? What's SubformControl, the combobox control name? Or isn't cboPickAddressType the combobox?

Ok, sorry, assumption made based on unfounded optimism..

To refer to a control on a subform, as your combobox is, you use this syntax:

Forms!{Main Form Name}!{subform CONTROL name}!FORM.ControlName

So if your Main form is called FormA, and the control name on it that holds the subform is sfrmCtrlB, and the combobox here is "Combo1", then the reference to is is

Forms!formA!sfrmCtrlB!FORM.Combo1

Note that you use the name of the CONTROL the subform is in, not the name of the subform itself (unless they are the same).

The rest of the code just sets the FILTER string for the subform, and turns FILTERING on.

HTH - and as you've noticed, Bob's suggestion to use tab controls was seconded by you, proving once again that great minds think alike..

JMH


Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[nonturbo]

I never realized this was going to be such a pain in the butt! I'm very close to just 86ing the whole combo-box address type idea.

Here's the code I've got on the After Update property of the Combobox.

Forms!PartnersData![Address Data].Form.Filter = "AccountNbr=" & Me.Combo25
Forms!PartnersData![Address Data].Form.FilterOn = True
Forms!PartnersData![Address Data].Form.Refresh

This doesn't give me any runtime errors, but the combobox still comes up as it did without the filter, showing all records, and after I select any record in the list, my whole subform now disappears.

..Maybe there is a better way to store multiple addresses for each account. We need at least the following address types with the ability to add more in the future (not all accounts have all): Home, Business, Account, Alternate, and Mailing. For data-integrity, I do not want multiple addresses of the same type for any one account. For instance, an account should not be allowed to have 2 home addresses.

As we've already concluded, I want a combobox on the subform that will say: Home, Business, Account, Alternate, Mailing. The combobox should get this information from a table (AddressTypes), which only has two fields: ID (Autonumber) and Address Type (Text).
The addresses themselves are stored in a table called AddressData with a field AddressID that determines the address type. The AddressData table is linked to the PartnersData table by a field AccountNbr.

Confused yet? I sure am!

As I stated above, I want to remove the built-in Access Navigation controls and use this combobox to select the Address Type. If the underlying queries do not find a home address for accountnbr X then a new record should be created. Switching the combobox to Business should then pull up accountnbr X's business address if it exists or create a new record if not. Switching back to home will pull back up the home address.

Is what I am trying to do really all that difficult, or am I just completely clueless about something? Please (ANYONE!) help. Thanks!!!!!!!!!

 

[nonturbo]

...Anyone?