I would like to display the cityname, state (if applicable), & country name within the message in the message box. these values come from the form CountryRecid, combo. column(1) and StateRecid.column(1) and the city name which is txtbox.
Private Sub cmdAdd_Click()'Dim gCountryID As Integer'Dim gStateID As Integer'Dim gCityName As StringDim n As IntegerDim nName As String On Error GoTo cmdAdd_Click_ErrorgCountryID = Nz([Forms]![frmAddLocation]![CountryRecID])gStateID = Nz([Forms]![frmAddLocation]![StateRecID])gCityName = Nz([Forms]![frmAddLocation]![CityName]) n = DCount("*", "tblCity", "[CityName] Like '*" & gCityName & "*' " & _ "AND [CountryRecID] = " & gCountryID & " " & _ "AND [StateRecID] = " & gStateID & "") If n = 0 Then DoCmd.RunSQL _ ("INSERT INTO tblCity ( CountryRecID, StateRecID, CityName, TaxInclusive, Top5000, DateCreated, Modifiedby ) " _ & "VALUES ( '" & gCountryID & "', '" & gStateID & "', '" & _ gCityName & "', " & gTaxIncl & ", " & gTop & ", Date(), '" & _ GetgUserID() & "' )") Else[b]' nName = gCityName & ", " & [Forms]![frmAddLocation]![StateRecID].Column(1) & ", " & [Forms]![frmAddLocation]![CountryRecID].Column(1)' Debug.Print nName Select Case MsgBox("Records Found & ", " & gCityName & , vbYesNo Or vbExclamation Or vbDefaultButton1, ")[/b] Case vbYes Case vbNo End Select End If On Error GoTo 0 Exit SubcmdAdd_Click_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAdd_Click of VBA Document Form_frmAddLocation"End Sub
You describe it well, but I'm not sure whether you are trying to put this in the messagebox in the error handler, or somewhere else within your code. The way to create a string variable with that information would look something like:
You describe it well, but I'm not sure whether you are trying to put this in the messagebox in the error handler, or somewhere else within your code. The way to create a string variable with that information would look something like:
strLocation = (me.txt_CityName + ", ") & (me.cbo_StateRecID.column(
& me.cbo_CountryRecID.Column
I used the + sign to concatenate some of these strings to the associated commas so that the comma will not appear if the City or State value is NULL.