Problem with bound combo box

L

Little Penny

I have a table of Laptop Computers (tbl_Laopts) that has a foreign key (UserID) with is link to the user info table(tbl_UserInfo) primarykey (UserID).
I created a form for Laptop table and on that form so that I can show user assigned if any I created 6 combo boxes that are bound to the UserID Field
by way of query. In this form the User ID is column 0. it works great.

MY Code:

Private Sub Form_Current()
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboLName.Value = Me.UserID.Column(2)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboLName.Value = Me.UserID.Column(2)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Private Sub UserID_AfterUpdate()
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboLName.Value = Me.UserID.Column(2)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Works Great........

So I then decided for ease of use to do it the for the computers table but with a twist I rearranged the columns so the when you click on the arrow of
the UserID field, it shows you the list but the last name is first followed by the first name and than the UserID which I believe to now be column 2.
I also rearranged the query to match the columns, but it does not work. In the properties of the UserID form field I changed the bound colum to 2. But
if I change it to 1 the user ID show up in the right field but none of the other info.

Here is my new code
Private Sub cboLocationID_NotInList(NewData As String, Response As Integer)

On Error GoTo cboLocationID_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Loaction ID " & Chr(34) & NewData & _
Chr(34) & " is not currently in loaction list." & vbCrLf & _
"Would you like to add the new LoactionID to the list now?" _
, vbQuestion + vbYesNo, "Location ID")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_Location([LocationID]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new location has been added to the list." _
, vbInformation, "Location ID"
Response = acDataErrAdded
Else
MsgBox "Please choose a Location ID from the list." _
, vbInformation, "LocationID"
Response = acDataErrContinue
Me.cboLocationID.Undo
End If
cboLocationID_NotInList_Exit:
Exit Sub
cboLocationID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboLocationID_NotInList_Exit
End Sub





Private Sub cmdNewUser_Click()
DoCmd.OpenForm "frm_UserSearch_Dialog", acNormal
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
If MsgBox("Changes have been made to this record" _
& vbCrLf & "Do you want to save these changes?" _
, vbYesNo + vbDefaultButton2, "Changes Made...") = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
Else
Cancel = True
Me.Undo
Me.cboLName.Value = Me.UserID.Column(0)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End If
End If
End Sub

Private Sub Form_Current()
Me.cboLName.Value = Me.UserID.Column(0)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.cboLName.Value = Me.UserID.Column(0)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Private Sub UserID_AfterUpdate()
Me.cboLName.Value = Me.UserID.Column(0)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub
 
S

Steve Schapel

Little Penny,

I believe part of the confusion may arise from the Column(x) property of
the combobox is zero-based, but the Bound Column property is one-based.
I.e. if the Bound Column is set to 2, then this would be referenced as
NameOfCombobox.Column(1).

I am happy to know that you have a solution that works for you.
However, if I can point out that there is no need for the other controls
to be comboboxes - it would be just as fine, and possibly tidier, to use
textboxes. Moreover, manipulating these in code is also unnecessary -
you could simply set the Control Source of the controls to, for example...
=[UserID].[Column](1)

Having said that, it would probably be easier still to simply include
the UserInfo table in the query that the form is based on, and then you
can represent the user's name, dept, etc directly on the form bound to
the fields in the query.

You may find this article of interest...
http://accesstips.datamanagementsolutions.biz/lookup.htm

--
Steve Schapel, Microsoft Access MVP


Little said:
I have a table of Laptop Computers (tbl_Laopts) that has a foreign key (UserID) with is link to the user info table(tbl_UserInfo) primarykey (UserID).
I created a form for Laptop table and on that form so that I can show user assigned if any I created 6 combo boxes that are bound to the UserID Field
by way of query. In this form the User ID is column 0. it works great.

MY Code:

Private Sub Form_Current()
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboLName.Value = Me.UserID.Column(2)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboLName.Value = Me.UserID.Column(2)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Private Sub UserID_AfterUpdate()
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboLName.Value = Me.UserID.Column(2)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Works Great........

So I then decided for ease of use to do it the for the computers table but with a twist I rearranged the columns so the when you click on the arrow of
the UserID field, it shows you the list but the last name is first followed by the first name and than the UserID which I believe to now be column 2.
I also rearranged the query to match the columns, but it does not work. In the properties of the UserID form field I changed the bound colum to 2. But
if I change it to 1 the user ID show up in the right field but none of the other info.

Here is my new code
Private Sub cboLocationID_NotInList(NewData As String, Response As Integer)

On Error GoTo cboLocationID_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Loaction ID " & Chr(34) & NewData & _
Chr(34) & " is not currently in loaction list." & vbCrLf & _
"Would you like to add the new LoactionID to the list now?" _
, vbQuestion + vbYesNo, "Location ID")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_Location([LocationID]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new location has been added to the list." _
, vbInformation, "Location ID"
Response = acDataErrAdded
Else
MsgBox "Please choose a Location ID from the list." _
, vbInformation, "LocationID"
Response = acDataErrContinue
Me.cboLocationID.Undo
End If
cboLocationID_NotInList_Exit:
Exit Sub
cboLocationID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboLocationID_NotInList_Exit
End Sub





Private Sub cmdNewUser_Click()
DoCmd.OpenForm "frm_UserSearch_Dialog", acNormal
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
If MsgBox("Changes have been made to this record" _
& vbCrLf & "Do you want to save these changes?" _
, vbYesNo + vbDefaultButton2, "Changes Made...") = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
Else
Cancel = True
Me.Undo
Me.cboLName.Value = Me.UserID.Column(0)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End If
End If
End Sub

Private Sub Form_Current()
Me.cboLName.Value = Me.UserID.Column(0)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.cboLName.Value = Me.UserID.Column(0)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub

Private Sub UserID_AfterUpdate()
Me.cboLName.Value = Me.UserID.Column(0)
Me.cboFName.Value = Me.UserID.Column(1)
Me.cboDept.Value = Me.UserID.Column(3)
Me.cboEXT.Value = Me.UserID.Column(4)
Me.cboCellPhone.Value = Me.UserID.Column(5)
Me.cboEmail.Value = Me.UserID.Column(6)
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
using multiple fields from a table 3
Not in List Event 4
commandbutton does not answer 0
type mismatch error in macro in excel 0
Control Bound Form Transactions 4
Prevent duplicate and certain types of entries 3
VBA works in orignal but fails in copy of database 1

Top