populate values on a text field with a combo box from another form

[ivonsurf123]

Hello,

I am trying to bring two values in a txtfield from one form to another form using a combo box to select the option I want, then when populate the values I want to see but it is not the option I want I have a clear button that does not work, when I choose the option on the combo box and populates the values from the other form it records the data as soon it is populate, I do not want that, in case I choose the wrong option. need help please

code:

Private Sub cboGoToPosition_AfterUpdate()
On Error GoTo ErrHandler:
 
    Dim str As String
    str = cboGoToPosition.SelText
    Dim dbTemp As Database
    Dim rsTemp As Recordset
 
    'Open connection to current Access database and perform the search
    Set dbTemp = CurrentDb()
    Set rsTemp = dbTemp.OpenRecordset("SELECT [REPLACEMENT FOR],[Position Name] From tbl_GCDS_Operations_Positions_Fills " _
                                      & "WHERE [REPLACEMENT FOR]= '" & str & "' and [Position Status] = 'Open'")
 
    Me.RecordSource = "SELECT * " & _
                      "From tbl_GCDS_Operations_Positions_Recruit " & _
                      "WHERE ((([Position Applied For])='" & Me.cboGoToPosition.Column(0, Me.cboGoToPosition.ListIndex) & "')) " & _
                      "ORDER BY[Position Applied For];"
    Me.Requery
 
    'Update fields if data is found
    If rsTemp.EOF = False Then
       Me.REPLACEMENT_FOR = rsTemp![REPLACEMENT FOR]
       Me.Position_Applied_For = rsTemp![POSITION NAME]
    Else
       Me.REPLACEMENT_FOR = ""
       Me.Position_Applied_For = ""
    End If
 
    rsTemp.Close
    Set rsTemp = Nothing
    Set dbTemp = Nothing
 
Exit Sub
ErrHandler:
    MsgBox Err.Description
End Sub
 
Private Sub cmdClearOpenPosition_Click()
 
 
    Me.RecordSource = "SELECT tbl_GCDS_Operations_Positions_Recruit.* " & _
                      "FROM tbl_GCDS_Operations_Positions_Recruit " & _
                      "ORDER BY tbl_GCDS_Operations_Positions_Recruit.[Position Applied For];"
    Me.Requery
    Me.cboGoToPosition = ""
 
End Sub

[twinnyfo]

ivonsurf123,

I would like to assist with your question, however, there seems to be a disconnect between your question and your code.

You stated:

I am trying to bring two values in a txtfield from one form to another form using a combo box to select the option I want

However, I do not see any references to that other Form, so I am not sure I quite understand your question.

There are some thing I can recommend based upon what I do see in your code which may be of help to you.

First, rather than changing the RecordSource of your Form every time you select something from your Combo Box, you should simply make tbl_GCDS_Operations_Positions_Recruit the RecordSource for your Form (in design mode).

Then, when you select an item from the cboGoToPosition Combo Box, Filter the Form. To clear the Form, remove the Filter:

Private Sub cboGoToPosition_AfterUpdate()
On Error GoTo ErrHandler:
 
    Dim str As String
    str = cboGoToPosition.SelText
 
    Me.Form.Filter = "[Position Applied For] = '" & str & "'"
    Me.Form.FilterOn = True
 
    Dim dbTemp As Database
    Dim rsTemp As Recordset
 
    'Open connection to current Access database and perform the search
    Set dbTemp = CurrentDb()
    Set rsTemp = dbTemp.OpenRecordset("SELECT [REPLACEMENT FOR],[Position Name] From tbl_GCDS_Operations_Positions_Fills " _
                                      & "WHERE [REPLACEMENT FOR]= '" & str & "' and [Position Status] = 'Open'")
 
    'Update fields if data is found
    With rsTemp
        If Not rsTemp.RecordCount = 0 Then
           .MoveFirst
           Me.REPLACEMENT_FOR = ![REPLACEMENT FOR]
           Me.Position_Applied_For = ![POSITION NAME]
        Else
           Me.REPLACEMENT_FOR = ""
           Me.Position_Applied_For = ""
        End If
        .Close
    End With
 
    Set rsTemp = Nothing
    Set dbTemp = Nothing
 
    Exit Sub
ErrHandler:
    MsgBox Err.Description
End Sub
 
Private Sub cmdClearOpenPosition_Click()
 
    Me.Form.FilterOn = False
    Me.cboGoToPosition = ""
 
End Sub

I also made a few minor changes to Lines 18 through 29 above. However, this code assumes that there will never be more than one record that matches the criteria you state. What happens if there is more than one record?

Additionally, based on line 7 above, since you are using the text value of a Combo Box and not its corresponding Index, it appears that your Tables may not be properly Normalized. If you are unsure of what that means, please refer to this Insight: Database Normalization.

Please let me know if you have additional questions or require additional clarification.

Hope this hepps!