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

NotOnList function, carry over three fields to Not On List add form.

P: 4
Hello thescripts!,

I hope this is easy for someone to answer, I just can't seem to figure it out.

On my main form, within a Tab Control I have a ComboBox named: GapArea that is filtered by three other ComboBox selections; Tier 1, Tier 2, Tier 3

I am using a notonlist event procedure as coded below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub GapArea_NotInList(NewData As String, Response As Integer)
  2. Dim Result
  3. Dim Msg As String
  4. Dim CR As String
  5. Dim ctl As Control
  6. Set ctl = Me!GapArea
  7.  
  8.     CR = Chr$(13)
  9.  
  10.     ' Exit this subroutine if the combo box was cleared.
  11.     If NewData = "" Then Exit Sub
  12.  
  13.     ' Ask the user if he or she wishes to add the new GapArea.
  14.     Msg = "'" & NewData & "' is not in the list." & CR & CR
  15.     Msg = Msg & "Do you want to add it?"
  16.     If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
  17.        ' If the user chose Yes, start the AddGapArea form in data entry
  18.        ' mode as a dialog form, passing the new GapArea name in
  19.        ' NewData to the OpenForm method's OpenArgs argument. The
  20.        ' OpenArgs argument is used in Customer form's Form_Load event
  21.        ' procedure.
  22.        DoCmd.OpenForm "frmGapArea", , , , acAdd, acDialog, NewData
  23.     End If
  24.  
  25.     ' Look for the customer the user created in the GapArea form.
  26.     Result = DLookup("[GapArea]", "Gap_Areas", _
  27.              "[GapArea]='" & NewData & "'")
  28.     If IsNull(Result) Then
  29.        ' If the GapArea was not created, set the Response argument
  30.        ' to suppress an error message and undo changes.
  31.        Response = acDataErrContinue
  32.        ctl.Undo
  33.        ' Display a customized message.
  34.        MsgBox "Please try again!"
  35.     Else
  36.        ' If the GapArea was created, set the Response argument to
  37.        ' indicate that new data is being added.
  38.        Response = acDataErrAdded
  39.     End If
  40.  
  41. End Sub
  42.  
This successfully copies the data the user tried to input into the GapArea that is not on the list into the frmAddGap.GapArea field.

My question is how can I also capture the three fields (Tier 1, Tier 2, Tier 3 the user has selected to arrive at that GapArea location and then populate those three fields in the frmAddGap just like the GapArea data?

Thank you sir or m'am for any insight you may provide.

Kind Regards,
Aaron
Jan 29 '08 #1
Share this Question
Share on Google+
1 Reply


P: 4
Hello thescripts!,

I hope this is easy for someone to answer, I just can't seem to figure it out.

On my main form, within a Tab Control I have a ComboBox named: GapArea that is filtered by three other ComboBox selections; Tier 1, Tier 2, Tier 3

I am using a notonlist event procedure as coded below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub GapArea_NotInList(NewData As String, Response As Integer)
  2. Dim Result
  3. Dim Msg As String
  4. Dim CR As String
  5. Dim ctl As Control
  6. Set ctl = Me!GapArea
  7.  
  8.     CR = Chr$(13)
  9.  
  10.     ' Exit this subroutine if the combo box was cleared.
  11.     If NewData = "" Then Exit Sub
  12.  
  13.     ' Ask the user if he or she wishes to add the new GapArea.
  14.     Msg = "'" & NewData & "' is not in the list." & CR & CR
  15.     Msg = Msg & "Do you want to add it?"
  16.     If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
  17.        ' If the user chose Yes, start the AddGapArea form in data entry
  18.        ' mode as a dialog form, passing the new GapArea name in
  19.        ' NewData to the OpenForm method's OpenArgs argument. The
  20.        ' OpenArgs argument is used in Customer form's Form_Load event
  21.        ' procedure.
  22.        DoCmd.OpenForm "frmGapArea", , , , acAdd, acDialog, NewData
  23.     End If
  24.  
  25.     ' Look for the customer the user created in the GapArea form.
  26.     Result = DLookup("[GapArea]", "Gap_Areas", _
  27.              "[GapArea]='" & NewData & "'")
  28.     If IsNull(Result) Then
  29.        ' If the GapArea was not created, set the Response argument
  30.        ' to suppress an error message and undo changes.
  31.        Response = acDataErrContinue
  32.        ctl.Undo
  33.        ' Display a customized message.
  34.        MsgBox "Please try again!"
  35.     Else
  36.        ' If the GapArea was created, set the Response argument to
  37.        ' indicate that new data is being added.
  38.        Response = acDataErrAdded
  39.     End If
  40.  
  41. End Sub
  42.  
This successfully copies the data the user tried to input into the GapArea that is not on the list into the frmAddGap.GapArea field.

My question is how can I also capture the three fields (Tier 1, Tier 2, Tier 3 the user has selected to arrive at that GapArea location and then populate those three fields in the frmAddGap just like the GapArea data?

Thank you sir or m'am for any insight you may provide.

Kind Regards,
Aaron
Not one to sit around and just wait to be saved, I managed to work this out and wanted to share what my solution was.

There's probably a better way, I'm not sure, but this works and that's what counts.

Because the only time the AddGap form is opened is due to the NotOnList event firing I just added three lines in the AddGap LOAD form event to copy the needed values from the open form.

Thanks to thescripts.com I found these!

Expand|Select|Wrap|Line Numbers
  1. Me.jca1ID = Forms!frmBin!JCAtier1ID
  2. Me.jca2ID = Forms!frmBin!JCAtier2ID
  3. Me.jca3ID = Forms!frmBin!JCAtier3ID
  4.  
All the best!
-Aaron
Jan 29 '08 #2

Post your reply

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