another combo box problem...with VB coding

rotschreck · Mar 10, 2000

Problem 1 
 
Using a combobox for Mining_Division, I am trying to write code to make a select case function that will send a letter value to Division_Letter. 
 
Private Sub Mining_Division_AfterUpdate(Cancel As Integer) 
Select Case Mining_Division 
Case Kenora 
Division_Letter = K 
Case "Larder Lake" 
Division_Letter = "L" 
End Select 
End Sub 
 
I have attempted to use the quote marks. I have attempted to remove the quote marks. 
 
Suggestion? 
 
Problem 2 
 
Using Date_Entered, I am trying to send the year involved to Year. 
 
Private Sub Date_Received_AfterUpdate(Cancel As Integer) 
Year = Year(Date_Received) 
End Sub 
 
What is wrong here? 
 
Thanks <a href=mailto: > </a> <a href=

http://www.geocities.com/siltharin>Rotschrck's

Eclectic Page</a>

DougP · Mar 10, 2000

If the variable Mining_Division contains a string 
then your case statements must be strings 
So 
 
Select Case Mining_Division '<<<<<< put a stop line here 
Case "Kenora" 
Division_Letter = K 
Case "Larder Lake" 
Division_Letter = "L" 
End Select 
End Sub 
 
Now if you are not sure what Mining_Division is or contains at the time it runs. 
Then put your cursor on the line above and Press the F9 key. 
It will put a red line there. 
Then run your code and it will stop on that line. 
Press F8 key once so it move to the next line. 
Press ctrl-G to bring up a debug window. 
Type in ?Mining_Division and press the enter key 
It will print out exactly what's in there at that moment in time. 
 
OK 
 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

JimHorton · Mar 10, 2000

Not sure if that's you're exact code, but have you tried prefixing with Me!, ie 
Select Case Me!Mining_division 
 
Also, is the bound column the one that returns, ie. "Larder Lake", or is it the ID? For this to work, it needs to be the string description, ie the "Larder Lake" column, unless you use: 
Me!Mining_division.column(1) or whatever column that division is in (zero based) 
 
 
--Jim

DougP · Mar 10, 2000

your second problem 
Year is a Function in Access VBA so you cannot use it for a variable. 
So I would use Year1 
 
Same thing as above you have to know what is in your variable. 
So is Date_Received a "Date" type or is it a String 
you could convert it to a date then get the Year like this 
 
Dim Year1 As Integer 
Year1 = Year(CDate(Date_Received)) 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

rotschreck · Mar 10, 2000

Doug, 
 
tried your suggestion. Still doesn't work. Maybe something to do with the (Cancel As Integer). Its a thought though I don't really know how it would affect it. 
 
Jim, 
 
Huh? 
 
I tried your Me! idea. It doesn't seem to work. I'm sure if I understood the .column(1) stuff I might be in a better position to figure this out. 
 
Thanks <a href=mailto: > </a> <a href=

http://www.geocities.com/siltharin>Rotschrck's

Eclectic Page</a>

JimHorton · Mar 10, 2000

R, 
In your select case, you seem to want to refer to the combo box value. Your reference to the combobox was not prefixed with either Forms!formname or Me!. This could cause a problem. 
 
Next, your example showed "Larder Lake" as one of the possible values. My question is: is the Bound column the one that contains values such as "Larder Lake" or is it, say, an ID field. Anyway, if you did Dougp's suggestion and put a breakpoint in the code, you should be able to see what that combobox is returning as a value. You can take it from there, but remember, you MUST put quotes around the values if they're characters. 
--Jim

rotschreck · Mar 15, 2000

Jim, 
The field Mining Division is text. I believe this is a string variable. I'm unsure why I would have to prefix with Forms!formname or Me! Since I'm in the current form. Maybe Table!tablename would work? 
 
Doug, 
I think my last post was unclear. I tried your suggestion with the year1 for the Year function. It doesn't seem to work. What should the (Cancel As _______) be? I'm unsure if it should be blank or As Integer or As Date. 
 
Thanks both for the help though.

<a href=mailto: > </a> <a href=

http://www.geocities.com/siltharin>Rotschrck's

Eclectic Page</a>

JimHorton · Mar 15, 2000

rotschreck, 
When you say 'field Mining Division', if you mean Field, as in a field in the table, bound to a textbox on the form, then you should prefix it with Me!. However, you said it's a 'string variable', so if it's a variable, where are you setting it's value?--If you are setting the value from the combobox, there are 2 issues: 
1. Don't name the variable the same as the field or combobox. 
2. I forgot what 2 was. 
3. Oh, now I remember. make sure the combobox's bound field is the one you really want, ie, if column 1 is bound, and column 1 is the description, this may not be what you want. A typical way is to show the description in the box, but bind the key--to do this you need to have description in column 1 and key in column 2, and set bound column to 2. Let me know if this works... 
--Jim

DougP · Mar 15, 2000

See back in your original post 
Private Sub Date_Received_AfterUpdate(Cancel As Integer) 
Year = Year(Date_Received) 
End Sub 
 
You cannot use "Year=" because "Year" is a Command word. 
So I added a 1 to the end. 
Year1 or it could be MyYear or whatever 
MyYear = Year(Date_Received) 
It cannot be Year by itself. 
If Date_Received is a String then use the Cdate to convert it to a Valid date, then the command word "Year" will extract the "Year" out of it. 
 
Where did the (Cancel As Integer) thing come from. 
Leave it alone. 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

rotschreck · Mar 15, 2000

Thanks Jim, 
That explains alot. 
 
Doug, 
I was thinking it might be the (Cancel As Integer) because I put it blank and it allowed another of my functions to work in the Date_Received_AfterUpdate. When I put Year, it all malfunctionned. I changed Year to Year1 as you suggested, but it didn't change anything. I understand that it will change everything, once I figure what is going wrong with my code. I've tried to change the (Cancel As Integer) to (), so that I could at least have one function that worked. Neither of them work now. 
 
Does this help? <a href=mailto: > </a> <a href=

http://www.geocities.com/siltharin>Rotschrck's

Eclectic Page</a>

DougP · Mar 16, 2000

When you say it doesn't change anything then I guess you are still getting an error? 
Do this 
Delete the whole function completly 
Then Compile the Code to see if you have any other errors 
Click the "Debug" menu then "Compile" 
If you have no errors open the form in design view. 
Double click on the combo box to bring up properties. 
then look for the "After_Update" event 
Click in the box and click "Event Procedure" 
Then click the "3 dots" button to bring up the VBA code window. 
Next put in the both of these lines 
Dim Year1 As Integer 
Year1 = Year(CDate(Date_Received)) 
and see what you get. 
OK 
This is a simple as it gets if you are still having errors then something else is corrupted in your database. 
Or tell us what the EXACT sytax of the Error, Not "Oh its Uh something like" 
 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

DougP · Mar 16, 2000

Also 
In your VBA code window put a ' "Single quote" in front of the 'On Error line so it will turn green and be a comment 
When you run your code it will stopon an erroe pop up a dialog box but it will have an additinal "Debug" Button click it an it will stop on the exact line causing the problem in your code. If you put your mouse over a variable and move it back kand forth slowly you should see a yellow box pop up with the value of that variable. 
 
Also you can Press Ctrl-G to bring up a Debug window and 
in the bottom half type a ? question mark and some variable that you want to examine 
like ?Year1 it will print out what Year1 is 
 
You can alos force the code to stop without an error 
By putting the mouse on a non DIM line and pressing the F9 key ti will put a line line ther and stop on it 
Then Press F8 to move one line at a time and in conjunction with the Debug window examine everything that going on 
you must pass a variable first using F8 before it will have a value. 
 
OK DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

another combo box problem...with VB coding

rotschreck

Programmer

DougP

MIS

JimHorton

Programmer

DougP

MIS

rotschreck

Programmer

JimHorton

Programmer

rotschreck

Programmer

JimHorton

Programmer

DougP

MIS

rotschreck

Programmer

DougP

MIS

DougP

MIS

Similar threads

Part and Inventory Search

Sponsor