Combo Box - using multiple times

[ezykiwi]

Hi again everyone,
With help on my first question Ive now got my cascading combo boxes working to a stage that shows all the information I need,

Currently Ive got 4 cbs and they work bring in and updating depending on what is selected in the previous etc etc.

combo1 = 2
combo2 = 5
combo3 = null
combo4 = null

how can I tell it to use combo2 since there is no values selected in 3 and 4

Ive currently got the combo boxes in a form of their own, basically in my main form Id like to be able to click on a button to update a value, and then it will bring up the form for the combo boxes, then id select the values till I had what i needed, and then it would fill in the value on the main form with my selection from the combo boxes,

to make it abit harder, on the main page, there is 3 values that will need to get their value from the combo boxes, will that just mean I have to make 3 forms with the combo boxes in them, one for each of the values on the main page, or could I just have one form and maybe put a button saying (Use this Selection for Value 1)(Use this Selection for Value 2) etc etc.

Ive looked through the net alot and cant seem to find any examples that suit my question... but if anyone knows of a good example that would be awesome.

Cheer Ezy

 

[ezykiwi]

ok here is what ive got so far, there are 3 combo boxes, and I have a textfield which is getting the value, based on the comboboxes having a value selected? It seems to be working, but I just wanted to make sure that im going about it the right way? Ive also set it up so that if the combobox has no options to choose from, it will be not visable on the form.

Option Compare Database
Private Sub cmdselectcat_Click()
If Me.combo3.Value >= 1 Then
Me.txtcatid = Me.combo3.Value
ElseIf Me.combo2.Value >= 1 Then
Me.txtcatid = Me.combo2.Value
Else
Me.txtcatid = Me.combo1.Value
End If
End Sub

Private Sub combo1_AfterUpdate()
Me.combo2.Requery
If Me.combo2.ListCount = 0 Then
Me.combo2.visible = False
Else
Me.combo2.visible = True
End If
Me.combo2 = Null
Me.combo3.Requery
Me.combo3 = Null
End Sub

Private Sub combo2_AfterUpdate()
Me.combo3.Requery
If Me.combo3.ListCount = 0 Then
Me.combo3.visible = False
Else
Me.combo3.visible = True
End If
Me.combo3 = Null
End Sub

 

[ezykiwi]

I guess my next question is, if ive got that part of it working well,

Ive got my main form,
Which has fields 3 differant feilds,

Say, A,B,C

What Id like to be able to do, is have a button next to each of those fields, which opens up the form with the cascading combos, and once ive selected the items from the combos, takes the variable from the text field, and puts it into the main forms field,

so if i click the button for say field C, and do the selections, it will then take the number from the text field in the combos menu, and place it as the value in variable C on the main form...

Any ideas on what I could look up to do this?
Cheers for the help

 

[CraigDolphin]

Expat Kiwi and Access novice myself

Why not store your cascading combo box output to a global/public variable instead of your text field. Then when you click your button next to A, B, or C, it first opens the (modal) cascading combo form. Then when you've made your selections (and thereby updated the global variable), close the combo box form and assign the value of the global variable to the desired field (A, B, C)

Assuming your two forms are named fmThreeFields (with textboxes: txtA, txtB, txtC) and fmSelections (with cmbo boxes cmbo1, cmbo2, cmbo3 etc)

I'd declare a public variable in a module with a function to retrieve it:

eg.,

 Public currentstring As String

Function Getcurrentstring()
Getcurrentstring = currentstring
End Function

Then in fmSelections use something like this in the after_update event of each cmbobox

if Me.cmbo1 & "" <> "" then currentstring = Me.cmbo1

And the code for your button (for example the button next to the txtA text box):

Dim strdocname as String
strdocname = "fmSelections"
DoCmd.OpenForm strdocname, acNormal, , , , acDialog
Me.txtA = Nz(Getcurrentstring(),"No Value Stored")

There may be better ways but this would work.

 

[ezykiwi]

Awesome, that looks great, cheers for the help, Ill give it a try shortly and let ya know how I get on....

Right, Update
It works a treat thanks again for the help, brillant, that is really awesome how it works, thanks for the help, brillant

 

[ezykiwi]

another quick question,

Can you make the popup form popup near the button? i know how I can have it in the top left. or centred, but can you position it better?

Cheers Ezy

 

[CraigDolphin]

Well, one way would be to make sure the autocenter and auto-resize properties are turned off and your border type is sizeable. Then, open the form and position and size it where you would like it on the screen, then right-click inside the form and enter design mode. Save the form while in design mode then close the form. Next time you open the form it should appear in that location again.

The problem with this is that if you, or someone else then 'moves' the popup its position may change. You might be able to avoid this by changing the border type again to something other than sizeable once it is appearing in the place you want (not sure, haven't tried it).

A more reliable method would be to code the position of the form in vba. See examples at http://www.mvps.org/access/forms/frm0042.htm to give you an indication as how you might do this.

Don't forget to do some searching via google or the search engine on this forum before you ask questions.

Cheers.

 

[ezykiwi]

Cheers for that Craig, I had seen that page when searching google, I was just wondering if there was a feature already in access that would let me do it,

Cheers once again for the help.

 

[ezykiwi]

I seem to have done something wrong,
It was working perfectly, but now Im getting the following error no matter what i select in the first combobox

"Expected variable or procedure, not module."

any ideas?

UPDATE = Silly me, I was playing around last night and renaming things to be more accurate but i renamed the module as the same name as the variable..... silly me....