Khalid-
In the form you open as a dialog, you should give them bound fields to enter both City and Country. Consider creating a "lookup" table that contains a list of all the valid countries you service. Use that to validate the Country name or perhaps use a combo box for Country on the AddCityCountry form.
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 5:11 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Done as you told, fine it is working now.
As the code and forms are working now, some questions are coming to my mind.
1- What if user does not enters Country, should i make a separate form like "EditCityCountry"
OR we can manage it here in some code?
2- Nice approach you wrote:
You should also have code in Before Update to ensure they've entered a valid Country name in addition to the city name.
Question is what is the check or criteria to ensure they've entered a valid Country name?
Regards,
Khalid
,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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@... [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,
(Message over 64 KB, truncated)
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
(Message over 64 KB, truncated)
__._,_.___
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 (18) |
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