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

 

[pbaldy]

A common way to debug SQL problems is the Immediate window. Use a variable for the SQL and see if this helps you:

http://www.baldyweb.com/ImmediateWindow.htm

 

[ivonsurf123]

Thank you, will do.

 

[pbaldy]

No problem, post back if you're still stuck.

 

[ivonsurf123]

By the way I change the query to:
Me.RecordSource = "SELECT * " & _
"From tbl_GCDS_Operations_Positions_Recruit " & _
"WHERE (((tbl_GCDS_Operations_Positions_Recruit.[Position Applied For]) Like '" & Me.cboGoToPosition.Column(1, Me.cboGoToPosition.ListIndex) & "*" & Me.cboGoToPosition.Column(0, Me.cboGoToPosition.ListIndex) & "*')) " & _
"ORDER BY tbl_GCDS_Operations_Positions_Recruit.[Position Applied For];"

Debug.Print Me.RecordSource
Me.Requery

and removed:

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

because it was adding a record without asking to, this combo-cox should just filter no add a record.

But now, I have another question, If I want to use an Add button, how can I add the record from my combo-box into the form if that's the person and position I want to add in the Recruits form, something like:

If record new then
Add details of Combo-box...

Need some help on this one. Thank you.