hi all im kinda stuck on one of the databases im trying to build and this certain line of code just wont do its job....
this code is behind a data input button to prevent duplicate names in a database....
i also keep getting this error.......... "Too Few Parameters. Expected 3."
___________________________________________
Private Sub addnewrec_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset (" SELECT names.fname, names.mname, names.lname FROM [names] WHERE (((names.fname) = [Forms]![names]![fname]) And ((names.mname) = [Forms]![names]![mname]) And ((names.lname) = [Forms]![names]![lname]))")
rst.MoveFirst
If rst.EOF = rst.BOF Then
DoCmd.GoToRecord , , acNewRec
Else
MsgBox "name already in database"
End If
End Sub
_____________________________________________
any help would be appreciated
cheers
dragon
2 2990
hi all im kinda stuck on one of the databases im trying to build and this certain line of code just wont do its job....
this code is behind a data input button to prevent duplicate names in a database....
i also keep getting this error.......... "Too Few Parameters. Expected 3."
___________________________________________
Private Sub addnewrec_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset (" SELECT names.fname, names.mname, names.lname FROM [names] WHERE (((names.fname) = [Forms]![names]![fname]) And ((names.mname) = [Forms]![names]![mname]) And ((names.lname) = [Forms]![names]![lname]))")
rst.MoveFirst
If rst.EOF = rst.BOF Then
DoCmd.GoToRecord , , acNewRec
Else
MsgBox "name already in database"
End If
End Sub
_____________________________________________
any help would be appreciated
cheers
dragon
First and foremost, your syntax is not correct: - Dim dbs As DAO.Database, rst As DAO.Recordset, strSQL As String
-
-
strSQL = "Select [fname], [mname], [lname] From tblEmployees Where [fname]='"
-
strSQL = strSQL & Forms!frmEmployees![fname] & "' And [mname] ='" & Forms!frmEmployees![mname] & "' "
-
strSQL = strSQL & "And [lname]='" & Forms!frmEmployees![LName] & "'"
-
-
Set dbs = CurrentDb()
-
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
-
-
Do While Not rst.EOF
-
'process Recordset here
-
'...
-
Loop
-
-
rst.Close
-
Set rst = Nothing
If rst.EOF = rst.BOF Then - the only time that this will happen is when there are no Records in the Recordset. This is not an approved Method, and would not work anyway: -
If rst.EOF = rst.BOF Then
-
DoCmd.GoToRecord , , acNewRec
-
Else
-
MsgBox "name already in database"
-
End If
this code is behind a data input button to prevent duplicate names in a database....
I think I have done something similar to what you are trying to do. My database will warn me, but not prevent me, from entering a record with the same info in one of my fields.
In the "Address" field on the form, I created an After Update event that checks the database for identical addresses in that field. If it finds one, it warns me with a message box that the address already exists in the database. Then I can decide for myself whether to continue creating the record.
My code looks like this: -
Private Sub ADDRESS_AfterUpdate()
-
-
If DCount("*", "SC_OPEN", "[ADDRESS]='" & Me!ADDRESS & "'") <> 0 Then
-
MsgBox "There is already an Open Order with this Address", vbOKOnly
-
End If
-
-
End Sub
-
Credit for this solution goes to the folks on this forum! Thanks again!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: William C. White |
last post by:
Does anyone know of a way to use PHP /w Authorize.net AIM without using
cURL? Our website is hosted on a shared drive and the webhost company
doesn't installed additional software (such as cURL)...
|
by: Albert Ahtenberg |
last post by:
Hello,
I don't know if it is only me but I was sure that header("Location:url")
redirects the browser instantly to URL, or at least stops the execution of
the code. But appearantely it continues...
|
by: James |
last post by:
Hi,
I have a form with 2 fields.
'A'
'B'
The user completes one of the fields and the form is submitted.
On the results page I want to run a query, but this will change
subject to which...
|
by: Ollivier Robert |
last post by:
Hello,
I'm trying to link PHP with Oracle 9.2.0/OCI8 with gcc 3.2.3 on a Solaris9
system. The link succeeds but everytime I try to run php, I get a SEGV from
inside the libcnltsh.so library.
...
|
by: Richard Galli |
last post by:
I want viewers to compare state laws on a single subject.
Imagine a three-column table with a drop-down box on the top. A viewer
selects a state from the list, and that state's text fills the...
|
by: Albert Ahtenberg |
last post by:
Hello,
I have two questions.
1. When the user presses the back button and returns to a form he filled
the form is reseted. How do I leave there the values he inserted?
2. When the...
|
by: inderjit S Gabrie |
last post by:
Hi all
Here is the scenerio ...is it possibly to do this...
i am getting valid course dates output on to a web which i have designed
....all is okay so far , look at the following web url
...
|
by: Jack |
last post by:
Hi All,
What is the PHP equivilent of Oracle bind variables in a SQL statement, e.g.
select x from y where z=:parameter
Which in asp/jsp would be followed by some statements to bind a value...
|
by: Sandwick |
last post by:
I am trying to change the size of a drawing so they are all 3x3.
the script below is what i was trying to use to cut it in half ... I
get errors.
I can display the normal picture but not the...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |