Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50

    Unanswered: Completely unbound forms / combo box

    OK here is the deal. I am trying to populate a test combo box on an unbound form with data from a Access table called tUsers. The only fields are UserID -- autonumber and Username -- Text. The combo box works but is displaying the autonumber ID instead of the UserName. I want it to display the username only.

    I know this can be done easily by linking the table to the form but I do not want to do this after all this is a learning experience only.

    Here is my code for the form load event.

    >>>>><<<<<

    Set rs2 As New ADODB.Recordset

    rs2.open ("SELECT UserID, UserName FROM tUsers ORDER BY UserName"), cn, adOpenOptimistic, adLockOptimistic

    Do While Not rs2.EOF
    cboUsers.AddItem rs2.Fields(0).Value
    rs2.MoveNext
    Loop
    rs2.Close
    Set rs2 = Nothing

    Call Display 'Fills in all fields on form.

    End Sub

    >>>>><<<<<

    How can i make this do what i want it to. Thanks a bunch

    I have tried the NewIndex thingy but can't get that to work. Not sure if that is a vb only thing or what.

    Thanks again

  2. #2
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    What you're doing here is that you're importing 3 fields but populating only the first column of the combobox. (field(0).value)

    you'll have to populate the other columns as well and then use the bound coulmn property to display the names as field(0) is the first column where you're importing ids and not the names...

    - Saqib

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Go to the properties of the combobox in question and adjust your Column Widths. Each column will have a width Ex: 1;2;3 For those columns that you want to hide set it's column width to 0.
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Ive tried the column width thing and when i do it doesn't let me save the value of the combo box. It is trying to save the username instead of the userid.

  5. #5
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Can you be more specific Saqib? I want to save the cboUsers data in another table linked to tUsers. I linked the users field by number only.

    I can get the username to be displayed in the combo box but it doesn't let me save it. I am unable to populate combo box with UserID and Username and then hide the id.

    I hope this makes sense.

    Thanks

  6. #6
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    What you do is

    1. Put a combobox on the form and then by right clicking it get its properties.
    2. Set following things
    2.1 Control Source UserID
    2.2 Rowsource Type as Table Query
    2.3 Row source (in there create your query from users table and just add username and userid as fields)
    2.4 put bound column as 2 userid is the second column
    2.5 Put limit to list and auto expand as Yes
    2.6 On Format tab of the properties Put column count as 2
    2.7 Put 2";0" as the width of the columns (what it does that it will display only the names and not the ids in the combobox)

    and then you'll have just names appering in the combo box and behind that ids will be saved in the field...

    I hope this would help.

    - Saqib

  7. #7
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    This form is completly unbound therefor i can't set the control source via properties. I am trying to do this with all code and nothing bound to a table.

    I'm trying to populate the combo box off of a query SELECT * FROM tUsers

    tUsers has 2 fields. UserID & UserName and is linked to tExercise via the UserID field.

    I can get my unbound form and combo box to save the selected user in the table tExercise but i have to know the UserID and not the username. I understand how to hide the primary key with bound forms but am unable to do it through code.

    Thanks Again Hooks

  8. #8
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    In that case if you're able to populate and display the names and id (hidden) in a combo box then use following in the after update event of the combobox

    Id_tobe_Saved.value = combobox.column(1)

    (here I'm assuming that 2nd hidden column contains user ids and first column contains names).

    * column(1) is actually the 2nd column as its index starts from 0 so 0 would be first column.

    - Saqib

  9. #9
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Thanks man I'll give it a try.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i wouldn't use SELECT * since it doesn't give you explicit control over the column order in the return. it is unlikely that someone or something will change the column order of the return, but it is easy to remove all doubt.

    SELECT UserID, UserName FROM as you had in your original post guarntees the column order.

    then:
    myCombo.ColumnCount = 2
    myCombo.BoundColumn = 0
    myCombo.ColumnWidths = "0;2"
    myCombo.RowSource = "SELECT UserID, UserName FROM etc etc

    and now:
    id_tobe_Saved = myCombo 'is enough: ID is the bound column = .value = default property for a combo

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Izy can you explain the last line a little bit more please.

    id_tobe_Saved = myCombo 'is enough: ID is the bound column = .value = default property for a combo

    Here is what i got so far.

    rs2.Open ("SELECT UserID, UserName FROM tUsers ORDER BY UserName"), cn, adOpenDynamic, adLockOptimistic

    Do While Not rs2.EOF
    cboUsers.AddItem rs2.Fields(0).Value
    rs2.MoveNext
    Loop

    cboUsers.ColumnCount = 2
    cboUsers.BoundColumn = 0
    cboUsers.ColumnWidths = "0;2"
    cboUsers.RowSource = "SELECT UserID, UserName FROM tUsers ORDER BY UserName"

    rs2.Close
    Set rs2 = Nothing

    Call Display 'Fills in all fields on form.


    When i click on the combo box i get "UserName FROM tUsers ORDER BY UserName" in the actual combo box.

    Do I have to have value list selected in the row source type property of the combo box?

    Do I have to have 0 in the bound column. I thought you would be able to leave all of this blank.

    Thanks Again.

  12. #12
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    I figured I would post all of my code. Thanks for helping. Im learning a lot.

    I know that there are some problems here but what i am worried about for now is just being able to populate the unbound combo box with the username and hide the userid and to be able to save the record correctly.

    Option Compare Database
    Option Explicit
    Private mboolAddMode As Boolean
    Private rs As New ADODB.Recordset
    Private rs1 As New ADODB.Recordset
    Private rs2 As New ADODB.Recordset
    Private cn As ADODB.Connection

    Private Sub cmdAdd_Click()

    Call ClearControls

    Me.AddMode = True

    Me.txtActivity.SetFocus

    End Sub

    Private Sub cmdClear_Click()

    Call ClearControls

    End Sub

    Private Sub cmdClose_Click()

    DoCmd.Close
    rs.Close
    Set rs = Nothing
    cn.Close

    End Sub

    Private Sub cmdDelete_Click()
    On Error Resume Next
    If MsgBox("Are you sure you wan't to delete record", vbOKCancel + vbExclamation, "Delete ???") = vbOK Then
    rs.Delete
    rs.MoveNext
    If rs.EOF Then
    rs.MoveLast
    ' MsgBox "Last Record"
    End If
    MsgBox "Record Deleted", vbInformation, "Delete Confirmation"
    Call Display
    Else
    End If

    End Sub

    Private Sub cmdFirst_Click()

    rs.MoveFirst
    Call Display

    End Sub

    Private Sub cmdLast_Click()

    rs.MoveLast
    Call Display

    End Sub

    Private Sub cmdNext_Click()
    rs.MoveNext
    If rs.EOF = False Then
    Call Display
    Else
    MsgBox "You are at the last Record"
    rs.MovePrevious
    End If

    End Sub

    Private Sub cmdPrevious_Click()
    rs.MovePrevious
    If rs.BOF = False Then
    Call Display
    Else
    MsgBox "You are at the First Record"
    rs.MoveNext
    End If

    End Sub

    Private Sub cmdRetrieve_Click()

    Call GetInfo

    Me.AddMode = False

    End Sub

    Private Sub cmdSave_Click()

    If Me.txtActivity = "" Then
    MsgBox "Activity must be filled in.", vbInformation, "Information"
    Me.txtActivity.SetFocus
    Exit Sub
    Else

    If Me.cboUsers = "" Then
    MsgBox "You must select a user.", vbInformation, "Information"
    Me.cboUsers.SetFocus
    Exit Sub
    Else


    If Me.AddMode = True Then
    rs.AddNew
    rs![Activity] = Me![txtActivity]
    rs![WorkoutDate] = Me![txtWorkoutDate]
    Me.txtMinutesExercised.SetFocus
    rs![MinutesExercised] = Val(txtMinutesExercised.Text)
    Me.txtMilesTraveled.SetFocus
    rs![MilesTraveled] = Val(txtMilesTraveled.Text)
    Me.txtCaloriesBurned.SetFocus
    rs![CaloriesBurned] = Val(txtCaloriesBurned.Text)
    rs![Notes] = Me![txtNotes]
    Me.txtWeight.SetFocus
    rs![Weight] = Val(txtWeight.Text)
    Me.cboUsers.SetFocus
    rs![User] = cboUsers ' Val(cboUsers.Text)
    rs.Update
    MsgBox "Record Saved"
    Else
    rs![Activity] = Me![txtActivity]
    rs![WorkoutDate] = Me![txtWorkoutDate]
    Me.txtMinutesExercised.SetFocus
    rs![MinutesExercised] = Val(txtMinutesExercised.Text)
    Me.txtMilesTraveled.SetFocus
    rs![MilesTraveled] = Val(txtMilesTraveled.Text)
    Me.txtCaloriesBurned.SetFocus
    rs![CaloriesBurned] = Val(txtCaloriesBurned.Text)
    rs![Notes] = Me![txtNotes]
    Me.txtWeight.SetFocus
    rs![Weight] = Val(txtWeight.Text)
    Me.cboUsers.SetFocus
    rs![User] = cboUsers 'Me.cboUsers.Column(0) 'Val(cboUsers.Text)
    rs.Update
    MsgBox "Record Updated"
    End If
    End If

    'If adding a record, set the ExerciseID text box to a new autonumber
    If Me.AddMode Then
    Me.txtExerciseID = ""
    End If

    'Turn the Add Flag to False
    Me.AddMode = False
    rs.Update
    'Me.cboUsers.Visible = False

    End If

    End Sub

    Private Sub Form_Load()

    Set cn = New ADODB.Connection

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=D:\ACCESSUNBOUND\unboundWorkoutData.mdb"

    rs.Open ("SELECT * FROM tExercise "), cn, adOpenDynamic, adLockOptimistic
    rs.MoveFirst

    rs2.Open ("SELECT UserID, UserName FROM tUsers ORDER BY UserName"), cn, adOpenDynamic, adLockOptimistic

    Do While Not rs2.EOF
    cboUsers.AddItem rs2.Fields(0).Value
    rs2.MoveNext
    Loop

    cboUsers.ColumnCount = 2
    cboUsers.BoundColumn = 0
    cboUsers.ColumnWidths = "0;3"
    cboUsers.RowSource = ("SELECT UserID, UserName FROM tUsers ORDER BY UserName")

    rs2.Close
    Set rs2 = Nothing

    Call Display 'Fills in all fields on form.

    End Sub

    Sub GetInfo()
    Dim ctl As Control
    Dim fld As Field

    Set rs1 = New ADODB.Recordset
    Me.txtActivity.SetFocus

    rs1.Open "SELECT * FROM tExercise Where Activity = '" & txtActivity.Text & "'", cn, adOpenDynamic, adLockOptimistic
    Debug.Print "Recordset Object Created"
    Call Display
    rs1.Close

    End Sub

    Public Sub ClearControls()

    Me.txtActivity = ""
    Me.txtCaloriesBurned = ""
    Me.txtExerciseID = ""
    Me.txtMilesTraveled = ""
    Me.txtMinutesExercised = ""
    Me.txtNotes = ""
    Me.txtWeight = ""
    Me.txtWorkoutDate = ""
    Me.cboUsers = ""

    End Sub

    Public Property Get AddMode() As Boolean
    AddMode = mboolAddMode
    End Property

    Public Property Let AddMode(ByVal boolNewValue As Boolean)
    mboolAddMode = boolNewValue
    End Property

    Private Sub Display()
    Me.txtExerciseID.SetFocus
    txtExerciseID.Text = rs!ExerciseID
    Me.txtMilesTraveled.SetFocus
    Me.txtMilesTraveled.Text = rs!MilesTraveled & ""
    Me.txtActivity.SetFocus
    Me.txtActivity.Text = rs!Activity
    Me.txtCaloriesBurned.SetFocus
    Me.txtCaloriesBurned.Text = rs!CaloriesBurned & ""
    Me.txtMinutesExercised.SetFocus
    Me.txtMinutesExercised.Text = rs!MinutesExercised & ""
    Me.txtNotes.SetFocus
    Me.txtNotes.Text = rs!Notes & ""
    Me.txtWeight.SetFocus
    Me.txtWeight.Text = rs!Weight & ""
    Me.txtWorkoutDate.SetFocus
    Me.txtWorkoutDate.Text = rs!WorkoutDate
    Me.cboUsers.SetFocus
    ' Me.cboUsers.Text = rs!User & "" ' rs!User & ""

    End Sub

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    since your SQL for this combo never changes, it makes more sense, takes less typing, and executes faster if you use a saved query to feed the combo.

    save yourself a query "qryCboUsers" that does
    SELECT UserID, UserName FROM tUsers ORDER BY UserName;
    once you've got that query working, then...

    Private Sub Form_Load()
    cboUsers.ColumnCount = 2
    cboUsers.BoundColumn = 0
    cboUsers.ColumnWidths = "0;3"
    cboUsers.RowSource = "qryCboUsers"
    cboUsers.Requery
    Call Display 'Fills in all fields on form.
    End Sub

    ...is all you need.
    to simplify your life further, why not do all this at design-time?? then you need no code at all! this combo .rowsource never changes, so design-time is fine.

    and by-the-way: filling a combo from a recordset doesn't happen the way you want it to happen in your code - it's actually a messy process involving a call-back function. doesn't seem worth the effort in your case!


    re:
    Izy can you explain the last line a little bit more please.

    id_tobe_Saved = myCombo 'is enough: ID is the bound column = .value = default property for a combo
    most objects (maybe all - who knows) have a default property. for a textbox or a combo, the default property is .value

    therefore...
    textbox = 99
    texbox.value = 99
    ...are equivalent statements

    and...
    aValue = combo.value
    aValue = combo.column(indexOfBoundColumn)
    aValue = combo
    ...are equivalent statements

    the .value of a combo is the value of the bound column in the current row.

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    and by-the-way: filling a combo from a recordset doesn't happen the way you want it to happen in your code - it's actually a messy process involving a call-back function. doesn't seem worth the effort in your case!


    I hate to ask this of you but can you explain how to fill a combo from a recordset please. I am really wanting to learn how to do this.

    thanks much

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mvps has a reasonable example of a callback list filler.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •