Solved Combobox Options

[Rob_Jones101]

Hi

I have a combo box with two options in it, Site or Yard. When I click site I want it to pull the address from another combo box and when I click Yard I want it to pull it from a different combo box. I have the code below but it dosen't seem to work properly. I think my IF statement might be faulty but i'm not sure. Any help would be appreciated.

Private Sub Cbodeladdress_AfterUpdate()

On Error GoTo Cbodeladdress_AfterUpdate_Error

If me.Cbodeladdress = "Yard" Then
Me.Txtaddress = Me.Cbodeladdress.Column(1)
Me.Txttown = Me.Cbodeladdress.Column(2)
Me.Txtcounty = Me.Cbodeladdress.Column(3)
Me.Txtpostcode = Me.Cbodeladdress.Column(4)
Me.Txtsitecontact = Me.Cbodeladdress.Column(5)
Else
if me.Cbodeladdress = "Site" Then
Me.Txtaddress = Me.Site.Column(1)
Me.Txttown = Me.Site.Column(2)
Me.Txtcounty = Me.Site.Column(3)
Me.Txtpostcode = Me.Site.Column(4)
Me.Txtsitecontact = Me.Site.Column(5)
End If

Cbodeladdress_AfterUpdate_Exit:

Exit Sub

Cbodeladdress_AfterUpdate_Error:

Select Case Err

Case Else

MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, "Error in module Cbodeladdress_AfterUpdate"
Resume Cbodeladdress_AfterUpdate_Exit

End Select

End Sub

 

[namliam]

Private Sub Cbodeladdress_AfterUpdate()

On Error GoTo Cbodeladdress_AfterUpdate_Error
       
If me.Cbodeladdress = "Yard" Then
        Me.Txtaddress = Me.Cbodeladdress.Column(1)
        Me.Txttown = Me.Cbodeladdress.Column(2)
        Me.Txtcounty = Me.Cbodeladdress.Column(3)
        Me.Txtpostcode = Me.Cbodeladdress.Column(4)
        Me.Txtsitecontact = Me.Cbodeladdress.Column(5)
Else 
        if me.Cbodeladdress = "Site" Then
                Me.Txtaddress = Me.Site.Column(1)
                Me.Txttown = Me.Site.Column(2)
                Me.Txtcounty = Me.Site.Column(3)
                Me.Txtpostcode = Me.Site.Column(4)
                Me.Txtsitecontact = Me.Site.Column(5)
End If

Cbodeladdress_AfterUpdate_Exit:

        Exit Sub

Cbodeladdress_AfterUpdate_Error:

        Select Case Err
      
                Case Else
      
                        MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, "Error in module Cbodeladdress_AfterUpdate"
                        Resume Cbodeladdress_AfterUpdate_Exit
       
        End Select
       
End Sub

Try making your code readable like above, can you spot your error now?

 

[MajP]

If me.Cbodeladdress = "Yard" Then
        Me.Txtaddress = Me.Cbodeladdress.Column(1)
        Me.Txttown = Me.Cbodeladdress.Column(2)
        Me.Txtcounty = Me.Cbodeladdress.Column(3)
        Me.Txtpostcode = Me.Cbodeladdress.Column(4)
        Me.Txtsitecontact = Me.Cbodeladdress.Column(5)
Elseif me.Cbodeladdress = "Site" Then
        Me.Txtaddress = Me.Site.Column(1)
        Me.Txttown = Me.Site.Column(2)
        Me.Txtcounty = Me.Site.Column(3)
        Me.Txtpostcode = Me.Site.Column(4)
        Me.Txtsitecontact = Me.Site.Column(5)
End If

The way you have it should have failed you would need another end if. You created a nested if then under the else.
Also check the bound column does it return the column with "yard" in it. Also the .column is zero based so column(1) is the second column of the rowsource.

 

[Rob_Jones101]

I have just tried a way around it by adding text boxes with the right information in it and making them invisible. This still isnt working.

Private Sub Cbodeladdress_AfterUpdate()

On Error GoTo Cbosite_AfterUpdate_Error

If Me.Cbodeladdress = "Site" Then
Me.Txtsite = Me.Cbosite.Column(0)
Me.Txtaddress = Me.Cbosite.Column(1)
Me.Txttown = Me.Cbosite.Column(2)
Me.Txtcounty = Me.Cbosite.Column(3)
Me.Txtpostcode = Me.Cbosite.Column(4)
Me.Txtsitecontact = Me.Cbosite.Column(5)
ElseIf Me.Cbodeladdress = "Yard" Then
Me.Txtsite = Me.TxtWhitland
Me.Txtaddress = Me.Txtwhitlandaddress
Me.Txttown = Me.Txtwhitlandtown
Me.Txtcounty = Me.Txtwhitlandcounty
Me.Txtpostcode = Me.Txtwhitlandpostcode
End If

Cbosite_AfterUpdate_Exit:

Exit Sub

Cbosite_AfterUpdate_Error:

Select Case Err

Case Else

MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, "Error in module Cbosite_AfterUpdate"
Resume Cbosite_AfterUpdate_Exit

End Select

End Sub

 

[Rob_Jones101]

Thanks MajP.

That worked a treat.