On Click Code Problem

[Pam]

I am having a problem with the following code:

Private Sub Combo32_Click()
If Me.PumpType = "MANUFACTURING" Then
DoCmd.OpenForm "fGeneralInfoManufacturing", , , "JobNumber=" &
Me!Combo32.Value
Else
DoCmd.OpenForm "fGeneralInfo", , , "JobNumber=" & Me!Combo32.Value
End If
End Sub

Combo32 is an unbound combo box on the fSwitchboard form. The following is
the Record Source for form "fSwitchboard":

SELECT qJobList.JobAssignment, qJobList.MachineAssignment,
qJobList.PumpType, qJobList.JobNumber, qJobList.CustomerName,
qJobList.ReceiptOfGoods, qJobList.PriceQuote, qJobList.ReadyToRepair,
qJobList.Completed, qJobList.HotJob, qJobList.MachineStart,
qJobList.MachineFinish FROM qJobList WHERE qJobList.JobAssignment='TechName"
Or qJobList.MachineAssignment='TechName';

As it stands now, the PumpType for the first job number in the list is what
is determining which form opens.
I would really appreciate any help with this!
Thanks,
Pam

 

[mscertified]

Put your code in the ON LOST FOCUS event.
When the click event is triggered the choice has not yet been applied.

-Dorian

 

[Pam]

It's still not opening the correct form based on PumpType.
Thanks, Pam

 

[Pam]

I received a reply to this, but I posted back that it still didn't work.
I'm hoping someone will please tell me why this is not working. Thanks in
advance for any help given!! Pam

 

[Pam]

I received the reply below, but posted back that it still wasn't working.
I'm hoping someone will please let me know why it won't work. Thanks in
advance for any help given!! Pam

 

[Wayne Morgan]

Is this a form in Continuous view or Single Form view? Where on the form
(which section) is the combo box located?

 

[Pam]

Wayne,

Thanks for looking at this for me. It is a Single Form view and the combo
box is in the detail section.
Pam

 

[Wayne Morgan]

Ok, you said "the PumpType for the first job number in the list is
determining which form opens."

It appears, looking at the code, that the form that will open depends on
what you have displayed in the field/control PumpType at the time you make
the JobNumber selection in the combo box.

What do you want to see happen?

 

[Pam]

Wayne,

What I want to happen is just as you stated the code should do. I want to
select a job number from the unbound combo box that contains a list of open
jobs. When I click on the job selected, I want it to open a form based on
the pump type for that particular job. I thought the code entered would do
the trick, but it either opens all jobs regardless of pump type with form
"fGeneralInfo" or "fGeneralInfoManufacturing". This is as it happened when
first opening the db today.

The first open job in the complete job list was a repair job (not
manufacturing). I entered into the combo box a job further in the list
(6875) that WAS a manufacuting job and it opened the form "fGeneralInfo" -
it should have been "fGeneralInfoManufacturing". When I changed the dates
around and made the first job in the list a manufacturing job, I opened the
same job (6875) and it opened "fGeneralInfoManufacturing" - as it should
have. Now the problem, I closed out the first job in the list, the
manufacturing job, and made the first a repair job and it still opens the
manufacturing form, as do all jobs regardless of pump types.

I hope this isn't too confusing. This one really has me stumped and I just
know it's going to be something simple I've overlooked.
Thanks, Pam

 

[Wayne Morgan]

You're not getting the type of job based on the job number from the combo
box, you're getting the type of job based on what is currently shown in the
text on the form at the time you make the selection in the combo box.

Example:
If the form shows a record and that record shows Manufacturing in the
PumpType textbox, then when you select a job number form Combo32, the form
fGeneralInfoManufacturing will open using the job number you selected,
whether or not that job number is a Manufacturing job. The form selection is
based solely on the value displayed on the current form. Since the combo box
is unbound, it is not linked to the current record on the form.

Are you wanting to open the new form depending on the PumpType of the job
number you just selected in the combo box instead of the PumpType showing on
the current form? If so, you'll need to do a DLookup() on the record for the
job number you just selected to get the PumpType for that job, then choose
which form to open.

 

[Pam]

Yes, I do want the new form to open depending on the PumpType of the job
entered to the combo box. Here is the DLookup code I entered to the
"OnClick" event of Combo32 and it only works somewhat. It will open the
correct form, but for every entry it opens with the job info (job number;
pump type, etc) for the first number on the list. This is very frustrating!!
I've only used DLookup one other place in db and copied it from post to
newsgroup.

Dim strFilter As String

strFilter = "JobNumber=" & Me.Combo32

Me.PumpType = DLookup("PumpType", "tGeneralInfo", strFilter)

If Me.PumpType = "NIKKISO/MANUFACTURING" Then
DoCmd.OpenForm "fGeneralInfoNikkiso"
Else
DoCmd.OpenForm "fGeneralInfo"
End If

Thanks again,
Pam

 

[Wayne Morgan]

You say it opens for the Job Number that is first in the list. Do you mean
you always get the job number that is first in the drop down portion of the
combo box instead of the one you've selected? If so, does the one you select
go into the combo box's textbox portion when you make the selection? Is
there more than one column in the combo box and, if so, which column is the
bound column? Have you tried the combo box's AfterUpdate event instead of
the Click event?

 

[Pam]

Do you mean you always get the job number that is first in the drop down

portion of the combo box instead of the one you've selected?

I get the job number that is first in the drop down of the combo on the new
form that opens.

If so, does the one you select go into the combo box's textbox portion when
you make the selection?
Yes.

Is there more than one column in the combo box and, if so, which column is
the bound column?
No.

Have you tried the combo box's AfterUpdate event instead of the Click
event?

AfterUpdate, OnClick, LostFocus and even tried coding in control source.
Now that I have it reading correct form, it won't open the selected job.

 

[Wayne Morgan]

When the new form opens, does it say "Filtered" down by the navigation
buttons? Your new code didn't include the WHERE clause for the form.

Dim strFilter As String

strFilter = "JobNumber=" & Me.Combo32

Me.PumpType = DLookup("PumpType", "tGeneralInfo", strFilter)

If Me.PumpType = "NIKKISO/MANUFACTURING" Then

DoCmd.OpenForm "fGeneralInfoNikkiso",,, strFilter

Else

DoCmd.OpenForm "fGeneralInfo",,, strFilter

End If

 

[Pam]

It FINALLY works!!! I typed in so much code in so many different ways and
left the filter off the code that worked!!! I can't thank you enough for
your time and help with these problems. I've been trying to solve them for
way too long. Thanks again, Pam