populate values on a text field with a combo box from another form (1 Viewer)

ivonsurf123

Registered User.
Local time
Today, 01:13
Joined
Dec 8, 2017
Messages
69
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
 

ivonsurf123

Registered User.
Local time
Today, 01:13
Joined
Dec 8, 2017
Messages
69
Thank you, will do.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:13
Joined
Aug 30, 2003
Messages
36,118
No problem, post back if you're still stuck.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:13
Joined
Feb 19, 2002
Messages
42,970
str = cboGoToPosition.SelText
Doesn't make any sense in this context. Combos choose values from The RowSource so you would never want to select only part of the value, plus, comobs are usually bound to an ID field and so you would never use the visible text to search for anything. And finally, to actually select the text, you would have to type the entry and then GO BACK and select some or all of the text prior to tabbing out of the field.

To refer to the bound value of the combo, use --- Me.cboGoToPosition

& "WHERE [REPLACEMENT FOR]= '" & str & "' and [Position Status] = 'Open'")
If the combo is bound to a numeric value, you would not encase it in quotes.
Dates are delimited with #'
Strings with single or double quotes
Numbers are NOT delimited.

And finally, this actually looks like a normalization issue. Usually, you would save the ID ONLY of the referenced record and would never copy additional fields. Of course, there are situations where you do need to copy data but I can't tell from what I see here whether or not that is the case.

Here's an example that might help you.
 

Attachments

  • FillFormFields.zip
    333.2 KB · Views: 80

ivonsurf123

Registered User.
Local time
Today, 01:13
Joined
Dec 8, 2017
Messages
69
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:13
Joined
Feb 19, 2002
Messages
42,970
For starters - tables hold data NOT forms. It is reasonable to have one form open another and using one of the arguments of the OpenForm method filter the data so the second form shows data related to the first form.

You're trying to add fancy stuff without understanding how forms actually work. You also said you wanted to get data from a combo but you are referencing a listbox. Unless you are looping though the listbox, there is no reason to reference anything except the "current" item. I haven't a clue what you think you are trying to get from the listbox.

If you could explain in non-technical terms what you are trying to do, we can probably provide a simpler solution.

Please look at the example I posted to see how to get values from a combo or listbox.
 

Users who are viewing this thread

Top Bottom