rst.open error

[Hildegoat15]

hi,

once agan i've stumped myself. i have a popup form that allows a user to enter a new City Code, City Name, and Province, and i'm trying to check for duplicates. my check for city code works fine, but when i check for duplicate city name/province combination, i get the error "No value given for one or more required parameters." the error is at the second rst.open line in the below code:

'Search for duplicate City Code. If a duplicate is not found, let the user continue; otherwise
' issue a messagebox and set focus to the City Code field
Set rst = New ADODB.Recordset
strQuery = "SELECT * FROM [Sleep - City Codes] WHERE str([CityCode]) = " _
& Me!txtCityCode & ""

rst.Open strQuery, CurrentProject.Connection, adOpenKeyset

If rst.BOF = True And rst.EOF = True Then
rst.close
Set rst = Nothing

'Searches for duplicate City/Province combination. If a duplicate is not found, continue;
' otherwise issue a messagebox and set focus to City Name field
Set rst = New ADODB.Recordset
strQuery = "SELECT * FROM [Sleep - City Codes] WHERE [CityName] = " _
& StrConv(Me!txtCityName, vbProperCase) & " AND [Province] = " _
& Me!txtProvince & ""

rst.Open strQuery, CurrentProject.Connection, adOpenKeyset

If rst.BOF = True And rst.EOF = True Then
rst.close
Set rst = Nothing

etc....

it seems like exactly the same syntax, except for a different SQL query. can anyone help me? -Matt

http://www.ranamedical.com

 

[SBendBuckeye]

Maybe you can make the process easier. Check out the Domain Aggregate functions in help. Several have the same basic syntax as follows:

DCount("[Field1]","[Table1]","[Criteria] = " & SomeCriteria)

The square brackets are required if there are any embedded spaces in your names. All 3 arguments must be surrounded by double quotes. Field1 is some field from the table you are searching. Table1 is the table you want to search. Criteria is like an SQL Where clause without the Where portion. The criteria can get a little tricky. A string value must be delimited by single quotes, a date value by pound signs and numberic data by nothing. Here are 3 examples to help you:

&quot;[Last Name] = '&quot; & cboAuthor & &quot;'&quot; <= Note single quotes
&quot;Birthday&quot; = #&quot; & SomeDateField & &quot;#&quot; <= Note pound signs
&quot;RecordID = &quot; & txtRecordID

Here is an example using DCount to see if your city code already exists on the Master file:

Dim intX As Integer

intX = DCount(&quot;[City]&quot;, &quot;[tblCity]&quot;, &quot;City = '&quot; & txtCity
& &quot;' And State = '&quot; & txtState & &quot;'&quot
If int > 0 Then
MsgBox &quot;Some error message&quot;
Else
proceed with adding the record
End If

Good Luck!