VBA form help

[labonte373]

Hi. I'm trying to code a textbox in VBA to look at a combo box for the data. I have a combo box that lists car numbers and serial numbers. I want the user to be able to click in the Model text box and have the correct Model fill in. I've tried an If..Then..ElseIf clause, If..Then, and select case. Part of it will work but not all of it.

Example, if car number 30 is chosen in the cboNumber box, then I want the user to click on the Model txtbox and automatically the model T_Fleet fills in. Can anyone help? Thanks

 

[RuralGuy]

Why not use the AfterUpdate event of the ComboBox to fill in the TextBox when the selection is made in the cbo?

 

[Shep]

Howsabout a little direct help to your actual question?

Novel idea, I know...


Me.txtWhatever = Me.cboWhatever.Column(x)

Where x = the column of the combobox which contains model. Remember that ComboBox column index begins with zero.

 

[labonte373]

Why not use the AfterUpdate event of the ComboBox to fill in the TextBox when the selection is made in the cbo?

RuralGuy,

Thanks for the response. I'm new to Access and VBA. I really do not understand what I need to do. I have a cboNumber box that has the car number and serial number ( [1] PRO730-562147). I want to be able to click in the txtModel box and have the correct model (T-FLEET) fill in automatically.

 

[labonte373]

Here is what I've done so far but it does not work

Private Sub cboNumber_AfterUpdate()
If cboNumber <= 30 Then
txtModel = "T-FLEET"
ElseIf cboNumber > 30 And cboNumber <= 55 Then
txtModel = "CARRYALL"
ElseIf cboNumber > 55 And cboNumber <= 86 Then
txtModel = "4PASSENGERS"
Else
txtModel = "6PASS, 8PASS, LONGBED"
End If

End Sub

 

[RuralGuy]

If the car number is always an integer then try:

Private Sub cboNumber_AfterUpdate()
Select Case CInt(Me.cboNumber)
   Case < 31
      Me.txtModel = "T-FLEET"
   Case 31 to 55
      Me.txtModel = "CARRYALL"
   Case 56 to 86
      Me.txtModel = "4PASSENGERS"
   Case Else
      Me.txtModel = "6PASS, 8PASS, LONGBED"
End Select

End Sub

It would be even easier and neater and more flexible if you join the Model type to the RowSource of the ComboBox. The method shown is basically hard coding these models into your software and the other method would simple be an entry in a table and the software would never know about the models.

 

[labonte373]

If the car number is always an integer then try:

Private Sub cboNumber_AfterUpdate()
Select Case CInt(Me.cboNumber)
   Case < 31
      Me.txtModel = "T-FLEET"
   Case 31 to 55
      Me.txtModel = "CARRYALL"
   Case 56 to 86
      Me.txtModel = "4PASSENGERS"
   Case Else
      Me.txtModel = "6PASS, 8PASS, LONGBED"
End Select

End Sub

It would be even easier and neater and more flexible if you join the Model type to the RowSource of the ComboBox. The method shown is basically hard coding these models into your software and the other method would simple be an entry in a table and the software would never know about the models.

Well I tried that and it didn't work either. I'm not sure how to add anything else to the row source as I've got the car/serial numbers joined there. Thanks

 

[RuralGuy]

Could you post the SQL for the RowSource of the cbo? Also which Column is the Bound Column? It is on the Data tab.

 

[boblarson]

And possibly an even quicker way to resolve this since it's gone several posts deep, is to post the database here so we can look and see exactly what you're doing. Sometimes we have a bit of a communication problem as you might be thinking some terminology means one thing and we think it means another.

So, if you do these steps to post:

1. Within your database go to Tools > Database Utilities > Compact and Repair

2. After that, zip the mdb file with WinZip, or some program like it.

3. After compacting and then zipping, the final .zip file needs to be 393KB or less to post here. To post, click the GO ADVANCED button down in the quick reply area at the bottom of all of the current posts. Then, scroll down and find the MANAGE ATTACHMENTS button. From there on out it is pretty easy.

 

[RuralGuy]

Mornin' Bob,
I wasn't trying for speed so much as showing everyone reading this thread how to go about solving this particular problem. If the OP wants to post the db, that is ok as well. How's the new job going?

 

[labonte373]

My db is way too large to post. I will post the most info I can.
RowSource type = Table/Query, SELECT [Car Numbers].[Car/Serial Number] FROM [Car Numbers];

 

[boblarson]

How's the new job going?

Just getting going. I don't actually start full-time with it until Monday, August 6th. Had to give my two weeks notice at my current job. Not that I WANTED to, but it was only fair. I, personally, would like to go bag the current gig and move to the next one immediately.

I am working part-time on it in the evenings and weekends. It should be interesting and fun.

 

[RuralGuy]

Well that explains why the code I supplied isn't working. You only have one field being returned in the ComboBox and it is the [Car/Serial Number] (a string) and not an Integer CarNumber. What fields are available in the [Car Numbers] table? Is there any table with the Models in it? If so, what fields are available?

 

[RuralGuy]

Bob,
I definately sounds like you are liking the new challenge. That's great.

 

[labonte373]

Well that explains why the code I supplied isn't working. You only have one field being returned in the ComboBox and it is the [Car/Serial Number] (a string) and not an Integer CarNumber. What fields are available in the [Car Numbers] table? Is there any table with the Models in it? If so, what fields are available?

Model is listed in the CarNumbers table as well as type and car/serial number.

 

[RuralGuy]

It is easier than you can believe to do what you want. Change the RowSource of the ComboBox to:

SELECT [CarNumber], [Car/Serial Number], [Model], [Type] FROM [Car Numbers];

then change the ColumnCount (on the Format tab) to 4 and the ColumnWidths to 0";whatever is there now;0";0"

I don't know if Model and Type are the name of your fields (hopefully not Type) but if the Model field has the Value for the txtModel control then the code becomes:

Private Sub cboNumber_AfterUpdate()
      Me.txtModel = Me.cboNumber.Column(2)
End Sub

The Column property is zero based so the Model column is the 3rd Column. Adjust the code so it works for you and post back if you need additional assistance.

 

[labonte373]

It is easier than you can believe to do what you want. Change the RowSource of the ComboBox to:

SELECT [CarNumber], [Car/Serial Number], [Model], [Type] FROM [Car Numbers];

then change the ColumnCount (on the Format tab) to 4 and the ColumnWidths to 0";whatever is there now;0";0"

I don't know if Model and Type are the name of your fields (hopefully not Type) but if the Model field has the Value for the txtModel control then the code becomes:

Private Sub cboNumber_AfterUpdate()
      Me.txtModel = Me.cboNumber.Column(2)
End Sub

The Column property is zero based so the Model column is the 3rd Column. Adjust the code so it works for you and post back if you need additional assistance.

When I changed the row source, the only things that show in the cbo is the model and type. Not sure what happened.

 

[RuralGuy]

Change the ColumnWidths to 1";1";1";1" and see what happens.

 

[labonte373]

Ok. I got that to work ok but the code that I typed into the txtModel does nothing.

me.txtModel = cboNumber.column(2)

I appreciate your help with this.

 

[labonte373]

I got it fixed on my own. Thanks for all your help and patience!