Using Info from Combo.Column.3 (1 Viewer)

[TVReplay99]

Good Day to all that read.

I have a transaction sub form and use a combo box to select the item and to populate the UnitPrice field. I have tried to use the 3rd column to open another form if it ="34-15"

My code:
Private Sub StockNo_AfterUpdate()
On Error GoTo Err_StockNo_AfterUpdate

Me.TransUnitPrice = Me.StockNo.Column(2)

If Me.StockNo.Column(3) = "34-15" Then
DoCmd.OpenForm "frmHazTrak"
Else
DoCmd.GoToControl "Quantity"
End If


Exit_StockNo_AfterUpdate:
Exit Sub

Err_StockNo_AfterUpdate:
MsgBox Err.Description
Resume Exit_StockNo_AfterUpdate

Nothing happens other than Control goes to Quantity. So to that end I conclude that my If..Then..Else Statement is incorrect. I have tried to write it many different ways but this is the only one that doesn't return an error message.

What the code is to check for is, if the item is a chemical open a form to enter Bottle Number and Room Location. The works but I can't get the users to it.

Any guidance is appricated,

Brian

 

[GumbyD]

Brian -

I think what is happening is that the code is pulling the first value for column three in your list - which I am guessing does not equal "34-15". What if you wrote the value for the field name for the "34-15" field (I am not sure what the field name for that one is) to another hidden field on the form. Then you could check the value of that field and open your pop-up form if it is "34-15".

Good luck!

GumbyD

 

[TVReplay99]

I tired creating an unbound field BOCode, Unbound because I don't need it in my tblTransDetails, it is stored in my tblStock. Creating that field was unsuccessful. It was left blank. my code for that was:
Me.BOCode = Me.StockNo.Column(3)

Thansk for the Reply any further suggestions are most welcome.

Brian

 

[Rich]

.Column(3) is the 4th column in your combo, is that the column you want?

 

[TVReplay99]

Yes

Brian

 

[Rich]

Set the control source of your unbound textbox to =[StockNo].[Column](3) and see what's returned

 

[TVReplay99]

I still get nothing. I even created a new text box, and nothing. I can't figure out why it works on one control and notr the other. Is it limited to one. Should i set the code to OnEnter of the Quantity text box (If I use the If . . .Then statement) but I don't think that would work either.

I am thinking about including BOCode in my tblTransDetail (Thinking that if it was bound it would work) to get this to work. But that would create redundat date in 2 tables and this is the only time that it would be used.

Thank you.

Brian

Another thought: Is there a way to set a default value using VBA? I tried it in Properties but that failed to work also.

 

[Rich]

Are you sure there's data in the 4th column? open the combo query and see what's being returned

 

[TVReplay99]

Rich'
Thanks for all of your help. I found anouther way to get it to work. I put that field in my Record Source Query for the subForm. I actually didn't know if it would work or not but in all of my trial and error it did.

I still can't figure out why it worked for one control and not the other.

Thanks again.

Maybe you can help out with another related problem. The Transaction form that I have is Password Protected (Not Access Security) from 2 other forms. Let me explain:
FrmPassword opens and user enters their Password, if correct frmAccount Opens and user selects which account to use from combo box.

What I want is to use the CustNo from frmAccountSelect cmboAccount.Column1 and .Column2 for AccountNo to populate the fields on my Transactions Form.

If you have any suggestions or a completely different solutions I would appreciate it.

Brian