Using If Statement in Form

[lansel]

I have a form with several fields. Two of the fields are [Machine] and [Machine Type]. Instead of typing in the machine type, the manager wants it to be automatically filled when the machine name is entered. I want to do an IIF statement in the control source, but I can't get it to work.

I have 8 machines and 3 machines types. I have put the IIf statement in the control source for the machine types but I keep getting a invalid syntax. I admit I have not written many if statements and it is probably something very easy that I am doing wrong, but I have spent hours trying to get this to work!!!

=IIf([Machine]=1-9514,[Machine Type],"XL",IIf([Machine]=2-9514,[Machine Type],"XL,IIF([Machine]=6-9514,[Machine Type],"XLS")))

There is a lot more, but this is a pretty good example of how I have it in the control source.

I would appreciate your help.

Thanks!

 

[Trevor G]

Perhaps try one of these,

Create a combo box that has 3 columns 1 machine type and then the other 2 columns deals with the other parts, you can then use some VBA in the Combo for After Update that would be something like this:

me.fieldname1.value=me.comboboxname.columns(1)
me.fieldname2.value=me.comboboxname.columns(2)

You can then set the column width of the combobox to shows columns 2 and 3 to size 0;0

That is one way. Another is to use the After Update of the Textbox which you fill in for the machine name to do the same sort of thing.

If me.textboxname="Machine Type one" then
me.textboxname1 = "Machine part 1"
me.textboxname2="Machine part 1a"
end if

then add the other IF statements for the other optins.

 

[Beetle]

Maybe we should take a step back before you go down the road of an over complicated nested IIf statement. Presumably you have a table for Machines and another for MachineTypes and these tables are related?

 

[lansel]

I have a Machine table and I have a Machine type table. Currently they are being selected by a drop down list. I thought that was fine, no problem, but I was asked to make the machine type automatically fill when the Machine name is entered.

 

[lansel]

Thanks for your replies. The After update sounds complicated to me, so I will have to try to figure it out. Would you put that in the control source of the text box?

 

[Beetle]

You don't necessarily need any event code to make this work. On your form you use a combo box (aka drop down list) to select the Machine. The Row Source of this combo box will select, for example, the MachineID and MachineName from the Machine table. In this Row Source query you join the MachineType table (the join fields would be MachineID or MachineTypeID, whatever the common field is) and add the MachineType field to the query, so MachineType is now in the third column of the query. It's a zero based index, so the first column is Column(0), the second is Column(1), etc.

Now, you add an unbound text box to your form and set its Control Source to;

=[YourMachineComboBoxNameHere].Column(2)

The one potential snag here is that if you are using a continuous form then this method won't work correctly.

Post back if you have questions.

 

[lansel]

Thanks,

I will spend time trying to get it to work this afternoon and if I have questions, I will ask for your help again!!

You guys are so amazing!

 

[Pat Hartman]

The syntax of your original IIf() is incorrect. The machine field is text so the values need to be enclosed in quotes - "1-9514". You are also missing a quote following the second instance of XL.

When you have a long list of conversions, the simplest solution is to create a table. You can then include the table in your query with a join to pick up the replacement value. the table is also easier to maintain than an IIf() stuffed in one or more queries.

 

[lansel]

Thanks to everyone!

I followed Sean's recommendation and I got the result I wanted! I never could get the IIF statement to work, so I gave up on that.

Thanks again!!