By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

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

P: 27
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboGoToPosition_AfterUpdate()
  2. On Error GoTo ErrHandler:
  3.  
  4.     Dim str As String
  5.     str = cboGoToPosition.SelText
  6.     Dim dbTemp As Database
  7.     Dim rsTemp As Recordset
  8.  
  9.     'Open connection to current Access database and perform the search
  10.     Set dbTemp = CurrentDb()
  11.     Set rsTemp = dbTemp.OpenRecordset("SELECT [REPLACEMENT FOR],[Position Name] From tbl_GCDS_Operations_Positions_Fills " _
  12.                                       & "WHERE [REPLACEMENT FOR]= '" & str & "' and [Position Status] = 'Open'")
  13.  
  14.     Me.RecordSource = "SELECT * " & _
  15.                       "From tbl_GCDS_Operations_Positions_Recruit " & _
  16.                       "WHERE ((([Position Applied For])='" & Me.cboGoToPosition.Column(0, Me.cboGoToPosition.ListIndex) & "')) " & _
  17.                       "ORDER BY[Position Applied For];"
  18.     Me.Requery
  19.  
  20.     'Update fields if data is found
  21.     If rsTemp.EOF = False Then
  22.        Me.REPLACEMENT_FOR = rsTemp![REPLACEMENT FOR]
  23.        Me.Position_Applied_For = rsTemp![POSITION NAME]
  24.     Else
  25.        Me.REPLACEMENT_FOR = ""
  26.        Me.Position_Applied_For = ""
  27.     End If
  28.  
  29.     rsTemp.Close
  30.     Set rsTemp = Nothing
  31.     Set dbTemp = Nothing
  32.  
  33. Exit Sub
  34. ErrHandler:
  35.     MsgBox Err.Description
  36. End Sub
  37.  
  38. Private Sub cmdClearOpenPosition_Click()
  39.  
  40.  
  41.     Me.RecordSource = "SELECT tbl_GCDS_Operations_Positions_Recruit.* " & _
  42.                       "FROM tbl_GCDS_Operations_Positions_Recruit " & _
  43.                       "ORDER BY tbl_GCDS_Operations_Positions_Recruit.[Position Applied For];"
  44.     Me.Requery
  45.     Me.cboGoToPosition = ""
  46.  
  47. End Sub
Dec 8 '17 #1
Share this Question
Share on Google+
1 Reply


twinnyfo
Expert Mod 2.5K+
P: 3,205
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboGoToPosition_AfterUpdate()
  2. On Error GoTo ErrHandler:
  3.  
  4.     Dim str As String
  5.     str = cboGoToPosition.SelText
  6.  
  7.     Me.Form.Filter = "[Position Applied For] = '" & str & "'"
  8.     Me.Form.FilterOn = True
  9.  
  10.     Dim dbTemp As Database
  11.     Dim rsTemp As Recordset
  12.  
  13.     'Open connection to current Access database and perform the search
  14.     Set dbTemp = CurrentDb()
  15.     Set rsTemp = dbTemp.OpenRecordset("SELECT [REPLACEMENT FOR],[Position Name] From tbl_GCDS_Operations_Positions_Fills " _
  16.                                       & "WHERE [REPLACEMENT FOR]= '" & str & "' and [Position Status] = 'Open'")
  17.  
  18.     'Update fields if data is found
  19.     With rsTemp
  20.         If Not rsTemp.RecordCount = 0 Then
  21.            .MoveFirst
  22.            Me.REPLACEMENT_FOR = ![REPLACEMENT FOR]
  23.            Me.Position_Applied_For = ![POSITION NAME]
  24.         Else
  25.            Me.REPLACEMENT_FOR = ""
  26.            Me.Position_Applied_For = ""
  27.         End If
  28.         .Close
  29.     End With
  30.  
  31.     Set rsTemp = Nothing
  32.     Set dbTemp = Nothing
  33.  
  34.     Exit Sub
  35. ErrHandler:
  36.     MsgBox Err.Description
  37. End Sub
  38.  
  39. Private Sub cmdClearOpenPosition_Click()
  40.  
  41.     Me.Form.FilterOn = False
  42.     Me.cboGoToPosition = ""
  43.  
  44. 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!
Jan 2 '18 #2

Post your reply

Sign in to post your reply or Sign up for a free account.