Access Combo VBA

[BobJ]

hi,

In my Orders form i have a combo box which determines where the order will take place. There are only 2 selections atm as we only have two sites..

What i need this combo box to do is instead of saving "Gatwick" when it is selected i need it to replace it with GWO- as this will be combined with an Order number eg (GWO-0001) and this GWO-0001 will be the actual Order number.

I wrote this code which obviously doesnt work otherwise i wouldnt be posting here! Is there any chance you could correct it for me and explain what i did wrong?

Much Appreciated!

Private Sub Combo96_AfterUpdate()

Dim ComboOption1 As String
Dim ComboOption2 As String

ComboOption1 = "Gatwick"
ComboOption2 = "Woking"

If AfterUpdate(Me!Combo96) = "Gatwick" Then
ComboOption1 = "GWO-"
End If

If AfterUpdate(Me!Combo96) = "Woking" Then
ComboOption1 = "WWO-"
End If

End Sub

 

[rainman89]

is there a field in your table that you are storing the area or are u storing the whole code(GWO-###) in a field?

 

[BobJ]

atm im storing the information in my Orders Table under "jobLocation" but that will only save the GWO-### after that i need to write some more code to combine them together i guess.. the problem is my current OrderID number is an autonumber and it wont let me change it to a number even if i remove all the relationships..

 

[rainman89]

ok then you would do

Private Sub Combo96_AfterUpdate()

Dim ComboOption1 As String
Dim ComboOption2 As String

ComboOption1 = "Gatwick"
ComboOption2 = "Woking"

If AfterUpdate(Me!Combo96) = "Gatwick" Then
me.joblocation = "GWO-"
End If

If AfterUpdate(Me!Combo96) = "Woking" Then
me.joblocation = "WWO-"
End If

End Sub

 

[BobJ]

ok but when i run that code i get an error saying:

Compile error wrong number of arguements or invalid property assignment

with the AfterUpdate highlighted:

If AfterUpdate(Me!Combo96) = "Gatwick" Then

which line depends on which option i select :s

 

[boblarson]

You don't want AfterUpdate(Me!Combo96)

You want

If Me!Combo96 = "Gatwick" ...

 

[BobJ]

thanks for the help guys its working now

 

[BobJ]

ok erm.. i think you can guess my next question..

what could be stopping my OrderID from changing from an autonumber to a regular number.. when i try it says i need to remove the relationships but ive done that i removed every single relationship and it still comes up with the same error.

 

[boblarson]

You can't convert an autonumber to another number. Create a new field for your number, make it a Long Integer and then use an update query to update it to the number in the autonumber column. Then you can delete the autonumber column.

 

[BobJ]

how come? i mean theres no data in my table so why can it not be done?

 

[boblarson]

Actually, I'm not sure - I just tested and I was able to do so when it didn't have any data whatsoever in the table.

 

[BobJ]

lol ok ill try out what you recommended

 

[BobJ]

k i've got that bit working now..

To get the joblocation and Orderid to merge into one would i use VBA on that specific form? im not really sure where to begin, but once i know where i need to do it im pretty confident i can do the rest

 

[boblarson]

You can concatenate to a text field like:

Me.YourTextBoxName = Me.YourJobLocationTextBoxName & Cstr(Me.YourOrderIDTextBoxName)

 

[BobJ]

ok, but i guess in order for that to work the user will have to enter the joblocation at the start?

 

[boblarson]

You can set that code in the form's BeforeUpdate event so it could be entered at any time before the form's update actually occurs.

 

[BobJ]

man... how long will it take for me to learn everything you guys know... ive been working with access since december and i even went on a couple courses... i still feel like a complete utter novice lol

Thanks for the help mate

 

[boblarson]

I've been doing it for over 10 years, and just in the last couple of years have I felt really comfortable with most of what I know (and I'm always learning something else here, either a better way to do it or the correct way because I was still doing it wrong) so it's a constant learning experience.

 

[BobJ]

ha sweet sounds like i have alot of fun and frustration ahead of me! lol