Combo box, load from table

  • Thread starter Thread starter Keith Hunter
  • Start date Start date
K

Keith Hunter

Hi all,

I'm another newbie to Access programming. What I am
trying to do is have a combo load a specific table field
based on the contents of the prior combo box selection.
I've been trying code-bits from various Access developer
manuals but the VBA debugger keeps popping up with
errors. I've copied the code from the form below. Any
suggestions (beyond getting a new line of work!) would be
greatly appreciated. Also... any suggestions on a good
book that covers VB/Access and the commands? Thanks. KH

code list:------------
Public banknum
Public itemdesc As String
Public eor As Integer

Option Compare Database

Private Sub Bank_LostFocus()
Set banknum = Me.Bank
MsgBox ("Bank Number is : " & banknum)---this was a test

End Sub

Private Sub Branch_Enter()

Dim db, rst As Recordset, strList As String
Set db = CurrentDb()
Set rst = db.OpenRecordset([Branch], dbOpenSnapShot,
dbForwardOnly)

If banknum = "0" Then
Do While Not rst.EOF
strList = strList & ";" & rst![Bank 17]
rst.MoveNext
Loop
Me![Branch].RowSource = Mid(strList, 1)

ElseIf banknum = "1" Then
Do While Not rst.EOF
strList = strList & ";" & rst![Bank 30]
rst.MoveNext
Loop
Me![Branch].RowSource = Mid(strList, 2)
ElseIf banknum = "2" Then
Do While Not rst.EOF
strList = strList & ";" & rst![Bank 31]
rst.MoveNext
Loop
Me![Branch].RowSource = Mid(strList, 3)
End If

End Sub
 
I like table/query sources better for comboboxes than value lists, but
that may be a style issue.

Your code tries to refer to the combobox (I hope) but uses the ! instead
of the . operator. I think.

Try changing "Me![Branch].RowSource" into "Branch.RowSource" (the Me is
superfluous)

By the other way, is the second parameter in that Mid() function
different (1, 2, 3) by your choice? Its effect will be to skip no, one
and two characters from the resulting value string.

If I misshot your problem, please state the compiler errors with spot
where they occur.

Keith said:
Hi all,

I'm another newbie to Access programming. What I am
trying to do is have a combo load a specific table field
based on the contents of the prior combo box selection.
I've been trying code-bits from various Access developer
manuals but the VBA debugger keeps popping up with
errors. I've copied the code from the form below. Any
suggestions (beyond getting a new line of work!) would be
greatly appreciated.
Click to expand...
Private Sub Branch_Enter()

Dim db, rst As Recordset, strList As String
Set db = CurrentDb()
Set rst = db.OpenRecordset([Branch], dbOpenSnapShot,
dbForwardOnly)

If banknum = "0" Then
Do While Not rst.EOF
strList = strList & ";" & rst![Bank 17]
rst.MoveNext
Loop
Me![Branch].RowSource = Mid(strList, 1)

ElseIf banknum = "1" Then
Do While Not rst.EOF
strList = strList & ";" & rst![Bank 30]
rst.MoveNext
Loop
Me![Branch].RowSource = Mid(strList, 2)
ElseIf banknum = "2" Then
Do While Not rst.EOF
strList = strList & ";" & rst![Bank 31]
rst.MoveNext
Loop
Me![Branch].RowSource = Mid(strList, 3)
End If

End Sub
Click to expand...
 
Back
Top