How to make Combo Box populate a different Form

[JohnD]

So I have seen this all throughout the forums but it all has to do with the same form and not a different one. I am utterly stuck with this.

Goal:
Have a basic form that has a combo box that displays the salesman for a car lot. User then selects a name out of the combo box. Now - there is a command button to open a new form. This button will populate the new form of all the customers for the salesman that is selected in the combo box.

Sound simple? Not really, im having a hell of a time.

 

[evanscamman]

You need to open the form using a filter:

Docmd.Openform "myform",,,"[SalesmanID] = " & ComboBox

 

[JohnD]

Thanks, I got it by the command button opening the customers form and having the control source reference the other forms combo box...

[forms]![Salesman_Form]![combo2]

Thanks!

 

[evanscamman]

If I understand what you are doing, it could get you into trouble that way, because you are relying on both forms being open. if the user closes form 1, then form 2 could break.

You might do better to open a filtered form.

Evan

 

[JohnD]

I know what you mean, but my plan was to take away the ability to close the form. The combo box is being made on a form that will allow the user to navigate the DB. You select the salesman and there will be several options for the salesman at that point - see statistics, pull sold customers, pull prospects and so forth, all from the main form and it will be locked to the left and will not be able to move nor close unless exiting the DB.

That being said, is there any other problems that might exist??

 

[evanscamman]

Ok.

Are you making the 2nd form 'modal'?
This should help keep the user from messing with the 1st one.

Evan

 

[JohnD]

What do you mean making the 2nd form 'modal'??

 

[ajetrumpet]

Before Mike answers here, I'd just like to say that he knows NOTHING.

 

[Mike375]

What do you mean making the 2nd form 'modal'??

When the form is made Modal it blocks you from doing anything else except what relates to that form. If you open a form in normal mode and then click anywhere on the form behind it then the form behind it will become the active form and move to the front.

When the open form is modal, if you click around it (on a bigger form) you just get....beep In short, the person using the computer must do what is required on the modal form or close the form. Often, such a form will have the close "x" removed and the form will only close when whatever action is required is taken, thus forcing the person to take the required action before moving on.

You set the form to modal in Form Design. Under properties click Other and you will see Modal Yes or No

 

[JohnD]

I like it!!! Thanks!

 

[Mike375]

There is also a sort of "in between" variety and that is Pop Up. That is changed on properties and just above where you do Modal Yes or No.

When the form is a Pop Up it always stays on top but you can still click on buttons, labels, type or whatever on the form behind it. Even if you click a button that opens another form from the form behind the Pop Up the pop up form still stays on stop. They can be quite handy for showing instructions etc as you can drag them around and out of the way but they sit there no matter what.

They are also good in a sales situation since they might have information the salesman wants and even though he types something in another form the pop up will stay on top. They are also excellent as the basis of a tabular type display of dates so you enter a call back day while making a few notes. If the prospect says.....I just realised that it will be better to call back on Jan 18....then you just click because the form was sitting there.

For someone in a sales environment like yourself it can prove beneficial to learn how to position the form when it opens, either by code or macro. In other words, when you open the form it will position itself a certain distance from the top and the left of the screen. This very useful when opening such a form as tabular date display. You have the form open so it might be at the far right of the screen. You can also control the height or perhaps I should say the length of a tabular type form.

 

[JohnD]

So I thought I had it

Looks like after running a query for my control source, its made the combo box useless. I did the query so my form would display the Salesman's name, not the ID.

After doing this, neither option works below:

For the Command Button...

DoCmd.OpenForm "Main_Customers", acNormal, , "[Salesman] = " & Me.Combo9

And inserting this under Salesman in the criteria of the query:
[forms]![selectSalesmanFrm]![combo9]

Both options worked before, but now...its nothing.

Am I missing something?


Oh, and thank you Mike. I will certainly implement what you suggested. I already found the code to size and position forms and will certainly use that as well!!!

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
    
    'just for testing
    'msgbox "InsideHeight = " & InsideHeight & vbCrLf & vbLf & "InsideWidth = " & InsideWidth
    
    InsideHeight = 8650 'twips is the unit of measurement
    InsideWidth = 3300
      
    DoCmd.MoveSize 100, 25, 3500, 9200

    
Exit_Form_Open:
    Exit Sub
    
Err_Form_Open:
    msgbox Err.Number & " - " & Err.Description
    Resume Exit_Form_Open
    
End Sub

 

[evanscamman]

Sorry I missed your question about Modal - but it looks like Mike explained it for you.

i don't think you need both a filtered query and filtered form.
Either way should work - just pick one.

If you may ever want to open the form unfiltered, then don't filter the query - filter the form in the docmd.openform event like you said.

I think you may be having problems if you are not using the SalesmanID right.

Your combo box should have 2 columns:

Column1: SalesmanID
Column2: SalesmanName

Make sure your table with the Salesmen's names has a primary key field set on autonumber. call this SalesmanID

In your combo box, set number of columns to 2
Set bound column to 1.
Anytime you refer to the value of the combobox in access, remember that you are refering to the ID - not the text of the name.

Set the width for your columns to: 0";1"
This makes the ID column invisible.

When you refer to the combo box in either your query's criteria or the filter statement in the openform command, make sure you say:
SalesmanID = ComboBox
not SalesmanName = ComboBox

Forgive me if I am telling you things you already know.
Trying to help,

Evan-

 

[JohnD]

Forgive me if I am telling you things you already know.
Trying to help,

Nope, I think you nailed it! I only have one column set on the combo box and its the salesman's names (text), not both like you suggest. The table is set up with a SalesmanID (PK) and then Salesman (regular text), nothing extra.

I probably know only 8-10% of Access, if that, so anything and everything is always of use to me

Will try it tonight when my kids are in bed - as for now, it's Daddy time with my girls!