Khalid-
You're pointing to the Response line, but I assume you mean the MsgBox line above. You will see that error only if the user closes form AddCityCountry without saving the new record. You should have Save and Cancel buttons on that form. Save will simply save the record and close the form. Cancel will do a Me.Undo and close. You should also have code in Before Update to ensure they've entered a valid Country name in addition to the city name.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On May 7, 2017, at 3:44 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
OK got it.
See below my question in comment below:
Private Sub cboResidenceCity_NotInList(NewData As String, Response As Integer)
Dim strName As String, strWhere As String
gstrAppTitle = "SAC Pakistan Guide"
' User typed in a City Name that's not in the list
strName = NewData
' Build the verification search string
strWhere = "[MainCityName] = '" & strName & "'"
' Verify that they want to add the new City Name
If vbYes = MsgBox("City Name " & NewData & " is not defined. " & _
"Do you want to add this City Name?", vbYesNo + vbQuestion + vbDefaultButton2, _
gstrAppTitle) Then
' Open the AddCityCountry form and pass it the new value
DoCmd.OpenForm "AddCityCountry", DataMode:=acFormAdd, WindowMode:=acDialog, _
OpenArgs:=strName
' Code will wait until "AddCityCountry" form closes - now verify that it got added!
If IsNull(DLookup("MainCityName", "MainCity", strWhere)) Then
' Ooops
MsgBox "You failed to add a City Name that matched what you entered. " & _
"Please try again.", gstrAppTitle
' Tell Access we handled the error, but cancel the update
Response = acDataErrContinue '<====== I DONT SEE THIS MSGBOX IN ANY SITUATION
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error message
MsgBox "This entry could not be found" & vbCrLf & _
"Please choose an item from the drop down list", vbInformation, "SAC Pakistan Guide"
Response = acDataErrContinue
cboResidenceCity.SetFocus
Me.Undo
End If
End Sub
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Kahlid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
That should work as long as gstrAppTitle has what you want to display.
The help file options are really obsolete. The MsgBox command was originally created when Windows was still using .hlp files. You needed a special utility to convert something like a Word document into help file format. The context argument would point to a specific numeric help topic within the file to display it in a separate help window. Help is now all xml-based, and I don't think Windows supports the old .hlp files anymore.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On May 7, 2017, at 2:06 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I have did as following and working, is it OK ?
Private Sub cboResidenceCity_NotInList(NewData As String, Response As Integer)
Dim strName As String, strWhere As String
gstrAppTitle = "SAC Pakistan Guide - Information" ' <======
' User typed in a City Name that's not in the list
strName = NewData
' Build the verification search string
strWhere = "[MainCityName] = '" & strName & "'"
' Verify that they want to add the new City Name
If vbYes = MsgBox("City Name " & NewData & " is not defined. " & _
"Do you want to add this City Name?", vbYesNo + vbQuestion + vbDefaultButton2, _
gstrAppTitle) Then
...
...
End Sub
By the way how does a help file is created ?
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
MsgBox syntax is:
MsgBox ( prompt [, buttons ] [, title ] [, helpfile ] [, context ] )
So, gstrAppTitle should be pointing to a help file. If you want to just display "SAC Pakistan Guide - Information" as the title, remove the last parameter.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On May 7, 2017, at 9:15 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Got it.
I commented out that statement thinking that after that i won't get access message. But now i understand that it was displaying because of the last word of the statement "Display" that forces the Response to display the access error message. (If i'm getting it right?)
Response = acDataErrDisplay
One more little thing:
Where can i put message title in:
' Verify that they want to add the new City Name
If vbYes = MsgBox("City Name " & NewData & " is not defined. " & _
"Do you want to add this City Name?", vbYesNo + vbQuestion + vbDefaultButton2, _
vbInformation, "SAC Pakistan Guide - Information", gstrAppTitle) Then
...
....
This gives me on Title of msgbox
64
Thank you very much for all the help & guidance.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Why did you comment out the statement:
Response = acDataErrDisplay
??
That tells Access to display the default error message. If you don't want that, use acDataErrContinue.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On May 6, 2017, at 10:56 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Sorry for late replying as i was on some field work.
Wao! that is great, marvelous, you described it very clearly step by step. The code is now as below, i'm yet getting one access message after last condition, if user types new and on message box clicks on 'No' button to "add" after my custom message, access display message "The text you entered is not....."
Private Sub cboResidenceCity_NotInList(NewData As String, Response As Integer)
Dim strName As String, strWhere As String
' User typed in a City Name that's not in the list
strName = NewData
' Build the verification search string
strWhere = "[MainCityName] = '" & strName & "'"
' Verify that they want to add the new City Name
If vbYes = MsgBox("City Name " & NewData & " is not defined. " & _
"Do you want to add this City Name?", vbYesNo + vbQuestion + vbDefaultButton2, _
gstrAppTitle) Then
' Open the AddCityCountry form and pass it the new value
DoCmd.OpenForm "AddCityCountry", DataMode:=acFormAdd, WindowMode:=acDialog, _
OpenArgs:=strName
' Code will wait until "AddCityCountry" form closes - now verify that it got added!
If IsNull(DLookup("MainCityName", "MainCity", strWhere)) Then
' Ooops
MsgBox "You failed to add a City Name that matched what you entered. " & _
"Please try again.", vbInformation, "SAC Pakistan Guide - Information", gstrAppTitle
' Tell Access we handled the error, but cancel the update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error message
MsgBox "This entry could not be found" & vbCrLf & _
"Please choose an item from the drop down list" _
, vbInformation, "SAC Pakistan Guide - Information" '<====AFTER THIS MSGBOX,GET
'Response = acDataErrDisplay 'ACCESS MSGBOX "The item you
cboResidenceCity.SetFocus ' entered is not......"
Me.Undo
End If
End Sub
'------------------------------------------------------------
Thank you so much for the help up to now.
regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Here's an example of a NotInList procedure that asks the user if they want to add a record and, if yes, opens a dialog add form and passes it what the user typed:
Private Sub RoomType_NotInList(NewData As String, Response As Integer)
Dim strName As String, strWhere As String
' User typed in a room type that's not in the list
strName = NewData
' Build the verification search string
strWhere = "[RoomType] = '" & strName & "'"
' Verify that they want to add the new Room Type
If vbYes = MsgBox("Room Type " & NewData & " is not defined. " & _
"Do you want to add this Room Type?", vbYesNo + vbQuestion + vbDefaultButton2, _
gstrAppTitle) Then
' Open the add a Room Type form and pass it the new value
DoCmd.OpenForm "frmRoomTypesAdd", DataMode:=acFormAdd, WindowMode:=acDialog, _
OpenArgs:=strName
' Code will wait until "add" form closes - now verify that it got added!
If IsNull(DLookup("RoomType", "tlkpRoomTypes", strWhere)) Then
' Ooops
MsgBox "You failed to add a Room Type that matched what you entered. " & _
"Please try again.", vbInformation, gstrAppTitle
' Tell Access we handled the error, but cancel the update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error message
Response = acDataErrDisplay
End If
End Sub
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On May 4, 2017, at 11:56 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I have no hesitation to admit that this code you told me is totally new for me and i have never used it or seen it before, therefore i'm still not following what you are telling me to do.
1) In form "frmAddressSubform" Combo Box "cboResidenceCity" its Control Source is "MainCityName" and Row Source is:
SELECT DISTINCT MainCity.MainCityName, MainCity.Country
FROM MainCity
ORDER BY MainCity.MainCityName;
Bound Column =1
Column Count=2
Which now shows "MainCityName" and "Country" in two columns.
On Not in List event i have removed the previous code, and now it is:
Private Sub cboResidenceCity_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "AddCityCountry", , , , , acDialog
End Sub
Is it right ?
Now if the "MainCityName" is not in the drop down list, would the user type only new city name or both city name and country like Test,Test1 ?
When i tried this i got error message "Invalid use of Null" and on clicking Debug button it shows highlighted the line:
intI = InStr(Me.OpenArgs, ",")
closing the code window, it opens Form "AddCityCountry" on first record, on entering new record as Test and Test1, the command button to save the record click event does not works
Private Sub CmdSaveRcrdAddCityCountry_Click()
Dim msg As String
msg = msg & "Do you want to Save this record?"
If MsgBox(msg, vbYesNo, "SAC Pakistan Guide - Confirmation!") = vbYes Then
DoCmd.Save
Else
Me.Undo
End If
End Sub
On clicking Close button, the form closes and immediately error message displays "The text you entered isn't an item in the list.
Also in the code you gave me for form "AddCityCountry" should i change them as follows marked with <====
Private Sub Form_Load()
Dim intI As Integer, strCity As String, strCountry As String
' If nothing in OpenArgs,
If Len(Me.OpenArgs) = 0 Then
' Nothing to do
Exit Sub
End If
' See if there's a comma
intI = InStr(Me.OpenArgs, ",")
' If no comma,
If intI = 0 Then
' Just set City
Me.MainCityName = Me.OpenArgs ' <=========
Else
' Set City and Country
Me.MainCityName = Left(Me.OpenArgs, intI - 1) ' <========
Me.Country = Trim(Mid(Me.OpenArgs, intI + 1))
End If
End Sub
I am totally confused. But also sure that you will put me on the right track and guide as you always did for me before, while i am learning something new for me from you.
Thanks in advance for help.
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
It's for the AddCityCountry form. The code extracts City and Country from the OpenArgs you pass from frmAddressSubform.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (16) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar