Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Hildegoat15 (TechnicalUser)
16 Aug 02 13:33
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 (Programmer)
16 Aug 02 20:15
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:

"[Last Name] = '" & cboAuthor & "'"   <= Note single quotes
"Birthday" = #" & SomeDateField & "#" <= Note pound signs
"RecordID = " & 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("[City]", "[tblCity]", "City = '" & txtCity
      & "' And State = '" & txtState & "'")
   If int > 0 Then
      MsgBox "Some error message"
   Else
      proceed with adding the record
   End If

Good Luck!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close