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

Go to specific field in form using "on not in list " in access

100+
P: 547
I have a combobox in a form that looks up records based on this form itself(cyclist tbl).I also add new records to this same form if i cant find the IDNo of a person in this combobox. If i type in the IdNo of a person in the combobox and dont find it, then i want to move straight to the IDNumber field (first field on the form) called IdNo with the "on not in list" and create a new record to start entering immediately.
Combo is called = Combo177
Form = cyclistf
Table = cyclist
fields:
IdNo = primary key + txt
Surname = txt
Name = txt
Gender = txt

This code ask the right questions, but dont copy this IdNo that it cant find into a new blank field for me. If i say no, then it gets into a loop and you can't get out until you say yes it must add it, which it does not do properly. I need to refresh the data somehow i think.
pls help!!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo177_NotInList(NewData As String, Response As Integer)
  2.  
  3.     On Error GoTo Combo177_NotInList_Err
  4.     Dim intAnswer As Integer
  5.     Dim strSQL As String
  6.     intAnswer = MsgBox("The Id number " & Chr(34) & NewData & _
  7.         Chr(34) & " is not currently listed." & vbCrLf & _
  8.         "Would you like to add it to the list now?" _
  9.         , vbQuestion + vbYesNo, "Entries")
  10.     If intAnswer = vbYes Then
  11.         strSQL = "INSERT INTO Cyclist([IdNo]) " & _
  12.                  "VALUES ('" & NewData & "');"
  13.         DoCmd.SetWarnings False
  14.         DoCmd.RunSQL strSQL
  15.         DoCmd.SetWarnings True
  16.         MsgBox "The new Id number has been added to the list." _
  17.             , vbInformation, "Entries"
  18.         Response = acDataErrAdded
  19.  
  20.  
  21.     Else
  22.         MsgBox "Please choose an Id Number from the list." _
  23.             , vbInformation, "Entries"
  24.         Response = acDataErrContinue
  25.         DoCmd.GoToRecord , "", acNewRec
  26.     End If
  27. Combo177_NotInList_Exit:
  28.     Exit Sub
  29. Combo177_NotInList_Err:
  30.     MsgBox Err.Description, vbCritical, "Error"
  31.     Resume Combo177_NotInList_Exit
  32.  
  33. End Sub
Nov 24 '10 #1

✓ answered by TheSmileyCoder

Im really at a loss to understand your points. You say one thing in your post, and then do the complete opposite in your code.

If you select yes, to the msgbox, you create the record, but don't go to it. If you select yes, you tell the user to try again, but use the code to go to a new blank record.

Anyways, I have made some modifications. I also STRONGLY suggest a more appropriate name then combobox177, an example could be: cmb_SelectCyclist
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_SelectCyclist_NotInList(NewData As String, Response As Integer)
  2. 'This combobox is used to navigate the form, or as a means to go to a new record for unknown cyclists.
  3. If vbYes= MsgBox("The Id number " & Chr(34) & NewData & _
  4.           Chr(34) & " is not currently listed." & vbCrLf & _
  5.           "Would you like to add it to the list now?" _
  6.           , vbQuestion + vbYesNo, "Entries") Then
  7.   me.cmb_Cyclist.Undo
  8.   Response=acDataErrContinue
  9.   DoCmd.GoToRecord , "", acNewRec
  10.   me.TextBoxBoundToIdField=newData
  11. else
  12.   Me.cmb_Cyclist.Undo
  13.   Response =acDataErrContinue 
  14. End If
  15. End Sub
  16.  
I dont have time to add more detailed comments/description,but hope you can either use this, or that it will point you in the right direction.

Share this Question
Share on Google+
6 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Im really at a loss to understand your points. You say one thing in your post, and then do the complete opposite in your code.

If you select yes, to the msgbox, you create the record, but don't go to it. If you select yes, you tell the user to try again, but use the code to go to a new blank record.

Anyways, I have made some modifications. I also STRONGLY suggest a more appropriate name then combobox177, an example could be: cmb_SelectCyclist
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_SelectCyclist_NotInList(NewData As String, Response As Integer)
  2. 'This combobox is used to navigate the form, or as a means to go to a new record for unknown cyclists.
  3. If vbYes= MsgBox("The Id number " & Chr(34) & NewData & _
  4.           Chr(34) & " is not currently listed." & vbCrLf & _
  5.           "Would you like to add it to the list now?" _
  6.           , vbQuestion + vbYesNo, "Entries") Then
  7.   me.cmb_Cyclist.Undo
  8.   Response=acDataErrContinue
  9.   DoCmd.GoToRecord , "", acNewRec
  10.   me.TextBoxBoundToIdField=newData
  11. else
  12.   Me.cmb_Cyclist.Undo
  13.   Response =acDataErrContinue 
  14. End If
  15. End Sub
  16.  
I dont have time to add more detailed comments/description,but hope you can either use this, or that it will point you in the right direction.
Nov 24 '10 #2

100+
P: 547
thx i will give it a try. sorry i copied this code from somewhere on the web and try to make it work. I am a bit of a novice at vba
Nov 24 '10 #3

100+
P: 547
this line seems to give vba error
Expand|Select|Wrap|Line Numbers
  1. Me.TextBoxBoundToIdField = NewData
see pic pls. i changed the combobox title
Attached Images
File Type: jpg nonnotinlisterror.jpg (64.8 KB, 159 views)
Nov 24 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
You have to replace the TextBoxBoundToIdField with the name of the control containing your id field (primary key)
Nov 25 '10 #5

100+
P: 547
Thx it working 100% now
Nov 25 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im smiling to hear that.
Nov 25 '10 #7

Post your reply

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