Combo Box Question?

[hardhitter06]

Hi all,

I apologize in advance because I am somewhat new to coding.

I have a combo box(called Category) with 3 types of forms to select from. Form A, Form B and Form C. Following that field is a [Contract #] field that corresponds with the form. If the user selects Form A or B, they will manually input the contract #. Since Form C is a special form, I need that Contract to be autonumbered each time that particular form is selected and flooded into the Contract # field.

For example, I have to enter a Form C document. I need that to be assigned a contract number of 1. Then I enter 2 form A's and a Form B...(or whatever). When I come back to entering another Form C, I need that autonumber to pick off from the previous one which would assign a contract number of 2.

How can I do this? Thank you for your help in advance.

Josh

 

[perez]

Hello,
I know that you are having problems today, but do you know of a
way to bind multiple columns to a combo box?
I can't seem to get my box to update all 3 columns of my list (since it can only be bound to one column at a time--at least thru the wizard).

Is there a work-a-round that you know of?

thx

 

[hardhitter06]

I'm sorry, I don't. But when i was searching for my problem I think I'm come accross something along those lines. Just search combo box or cascading combo box, I think that was the search I used...sry I couldn't be of any more help.

 

[Moniker]

Hello,
I know that you are having problems today, but do you know of a
way to bind multiple columns to a combo box?
I can't seem to get my box to update all 3 columns of my list (since it can only be bound to one column at a time--at least thru the wizard).

Is there a work-a-round that you know of?

thx

Perez,

To update the unbound columns, you need to reference each column separately in code. You can read (but not write to) to values of unbound columns like this:

ComboBoxName.Column(0) for the first column
ComboBoxName.Column(1) for the second column

And so on. Assuming you're attached to a table, you then update the underlying table using an Execute command. Note that using Execute does not invoke warnings (like "Are you sure you want to append?" and so on), so there's no need for SetWarnings=False. The update would look like this:

CurrentDb.Execute "UPDATE YourTableName SET YourTableName.FieldNameToUpdate = YourUpdateValue
WHERE YourTableName.BoundColumnName = '" & YourComboBoxName.Value & "';"

If you need a quick example, I can whip one up.

~Moniker

 

[boblarson]

And if you want to DISPLAY multiple columns, you need a listbox instead of a combo box as a combo box can show multiple columns in the drop down part, but not after a selection is made.

 

[hardhitter06]

Anyone have any help 4 me??

 

[boblarson]

This is not necessarily a "simple" thing but you would likely need to set the On Current event of Form C to look up the last value assigned and increment by one:

Look into using DMAX. I rarely use it so I never remember the exact syntax.

 

[hardhitter06]

Ehhhh, alright..

Would it be easier if i jus put a counter button next to the type of form i select, and tell the user if you use form c...select this button to generate a contract number(and then have that number flood into the contract number field)...or is that not really solid option?

 

[Scottyk537]

Not quite understanding your issue.
If form C is picked you want an autonumber filled in Contract, say #523
Form A is picked, user manualy types a number, say #524
Form C is picked, you want a autonumber to fill #525?

Does this autonumber only increment when Form C is picked?

Do you have a sample DB could look at?

 

[hardhitter06]

It's the same database...under category, i have 10 forms to choose from, lets jus say theres 3.

So form A comes...it has the contract number on it...i enter it into the field.

Form C is in front of me, this form will never have a contract number on it so it needs to be auto-numbered ...so lets say this is the first 1.

Form B comes...contract number on it, just fill it in.

2 more form Cs. so contract number 2 and 3...and so on.

So pretty much everytime i get a form C and select Form C from the category combo box...i need it to generate the next autonumber. Any other time im just reading off the contract number and filling it in the field after selecting the given form(category).

I hope that's clearer...

 

[hardhitter06]

Also, the forms don't need to be ordered, only the form c's do, i could get a form a thats 523, a form b thats 1023, but if i have C's back to back, then it has to be like 52 and 53...

 

[Scottyk537]

So it's OK to have Form A #523 and Form C #523?

 

[hardhitter06]

Yeah, that is fine.

 

[Scottyk537]

Will Form C, or whatever the keyword is, be included in your table?
If so, what field?

 

[hardhitter06]

On the contract input form, under the field category, Form C [or "MU"] is the option to select, and contract number should be flooded in once MU is selected from the drop-down list...or that's at least what I'm hoping for. If you have an alternative suggestion, the floor is yours.

Fields come from tblContract.

 

[Scottyk537]

I got it working.....
You might want to rename the form and query to fit your naming convension.
Remember that tables, queries, forms, controls, etc. should not have any () or spaces.
Let me know if you need me to explain what I did.
See attached......

 

[hardhitter06]

WOW...thank you!!

Looks like its working how i want it. One question tho, wut is that giant box next to the fields on the input box...?

 

[Scottyk537]

Oh, i forgot to tell you. That is a subform I had to bring in for the last count of "MU". It needs to stay on this form to work. In the design view of the input form, you can shrink the size of the subform to nothing so the user wouldn'd even know it's there.

 

[hardhitter06]

Sounds great Scott,

Thank you again. It says I have to spread reputation around, but once I'm able to reply to your help...I'll definately take care of that for you.

Have a good night,

Josh

 

[hardhitter06]

some more help (thankyou)

Scott (or anyone else),

There's a couple altercations I need to make to the code you helped me out with.

Private Sub Category_AfterUpdate()
Dim MUcounter As Long
MUcounter = [Forms]![frmContract(input)]![frmSubfrm]![Contract #]

If Me!Category = 7 Then
Me![Contract #] = MUcounter + 1: Me![Contract #].Locked = True
End If
End Sub


What I need to fix is a "MU" to show up infront of the number. Also, is there a way to have the numbers count up in this format, MU0008..MU0009...MU0010