My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Using Dlookup To Check If Record Exists
|
|
 Apr 26 2012, 10:58 PM
Post
#1
|
|
|
UtterAccess Enthusiast Posts: 57  |
Hi All~
I'm building an "Instructor DB where one of its functions will allow a teacher to add students to classes. There are multiple tables within the db, however the 1 concerned with here is tblRoster. There are 3 fields in that table- the PK, and 2 foreign keys to a Student and Class table. I've built a form that allows the teacher to add students to classes. In a college setting, a single student will probably enroll for multiple classes that an instructor teaches, but within the form, I want to ensure the teacher does NOT add a student to a class they've already been added to (but allow them to be added to other classes the teacher instructs). At first I used a DCount function which in testing mode obviously did not work- looking for FK's in the form that matched values within hidden text boxes based on a users selection in 2 combo-boxes (my test cases in the DB had 2 classes- 1 and 2, and 12 students {6 for each class- with 1 or 2 in their name for sanity checks}. The function found the studentID # in the domain and then found the ClassID # within the domain and thus wouldn't allow Student1 To be added to Class2. I switched to a DLookup instead, but am still running into the same problem. Clear eyes will most assuradly help in this matter! My VBA code: CODE Private Sub btnConfirm_Click() 'test query Dim CkStudID As Integer Dim CkClassID As Integer 'Error handling On Error GoTo ErrorHandler1 '---Before adding student to a class roster, first check if student is already enrolled in that class 'Domain count function: check if studentID exists in tblRoster already CkStudID = DCount("RosterStudent", "tblRosters", "RosterStudent = txtStudID.value") CkClassID = DCount("RosterClass", "tblRosters", "RosterClass = txtClassID.value") If (CkStudID >= 1 And CkClassID >= 1) Then 'If DCount("RosterStudent", "tblRosters", "RosterStudent = txtStudID.value") >= 1 Then 'If the studentID exists, now see if the same classID exists for that record in tblRosters 'If DCount("RosterClass", "tblRosters", "RosterClass = txtClassID.value") >= 1 Then 'If both exist, let the user know and whats going to happen next MsgBox "This student has already been added to this class. Roster addition is cancelled!" 'Return focus to the Student Lookup Combobox cboStudent.SetFocus 'Remove the potential record from the fields SendKeys "{esc}", True 'Cancel all actions Cancel = True 'Exit this subroutine Exit Sub End If 'If the classID doesn't exist, move on 'End If 'If the StudentID doesn't exist, move on '---add the record and move to a new record 'set focus back to the Student Lookup cboStudent.SetFocus 'Run the query behind the subform to display the added record DoCmd.Requery 'Move to a new record DoCmd.GoToRecord acDataForm, "frmRosterAdd", acNewRec 'Error handling ExitErrorHandler1: Exit Sub 'Error handling ErrorHandler1: MsgBox Err.Description Resume ExitErrorHandler1 End Sub Ignore the comments, I was in the process of adding new code and did not kill off the irrelevant stuff Thanks (IMG:style_emoticons/default/hat_tip.gif) |
 |
 
|
|
Apr 26 2012, 11:11 PM
Post
#2
|
|
|
UtterAccess Guru Posts: 857 From: Bogotá - Colombia |
I won't use DLookUp for those purposes.
What I'd do is to create a composite index in that table and use the function Seek() in a recordset to find if the student AND the class already exist in that table. This way is probably the fastest and most efficient way you can accomplish that task. Regards, Diego |
|
|
|
|
Apr 26 2012, 11:16 PM
Post
#3
|
|
|
UtterAccess Enthusiast Posts: 57 |
Any links that address this topic that you can think of?
|
|
|
|
|
Apr 26 2012, 11:27 PM
Post
#4
|
|
|
UtterAccess Enthusiast Posts: 57 |
I should clarify- perhaps relevant information (I know, Web searching is like hitting a hammer against your head, you'll rarely find an exact match to your issue). I've done some searching since your reply, the info is good, but I've never used the function and I'm a little buzzed right now- anything out there that explain things a bit simpler (lol!)
|
|
|
|
|
Apr 26 2012, 11:29 PM
Post
#5
|
|
|
UtterAccess Guru Posts: 857 From: Bogotá - Colombia |
Unfortunately not.
I'm an addict of the F1 key in access, so most of what I actually know comes from there. You can give it a shot! Try searching for Seek function in the Access help. For Seek() to work it needs the index!!! Regards, Diego |
|
|
|
|
Apr 27 2012, 12:00 AM
Post
#6
|
|
|
UtterAccess Guru Posts: 857 From: Bogotá - Colombia |
I wrote this example for you on the fly. You have to dimension it, and adapt it to your table fields.
CODE Set rst = dbs.OpenRecordset("YourTable", dbOpenTable)
rst.Index = "YourIndex" rst.Seek "=", Field1, Field2 If rst.NoMatch Then rst.AddNew rst!Field1= txt1 rst!Field2= txt2 rst.Update Else MsgBox "That record already exists" End If rst.Close This post has been edited by dipetete: Apr 27 2012, 12:23 AM |
|
|
|
|
Apr 27 2012, 12:39 AM
Post
#7
|
|
|
UtterAccess Enthusiast Posts: 57 |
Thanks for the code-
In thinking about the procedure I was previously using (I'm definitely going to try your code though), I didn't refine it enough to match the logic I was considering before posting it. In essance: I want to return a value(s) using the DLookup function; for example I want the ClassID that applies to a StudentID (supplied via textbox value). If a student hasn't been added to any class- no problem (the DLookup will return a null value), however if a student has been assigned to the class (by comparing the value in the other hidden textbox to what the DLookup returns), then don't add the record and let the user know. My question from here- the DLookup will only return the FIRST value found (if I add a student to a second class, the second ClassID won't be found running the procedure again and the student will be added to the second class again) Besides the seek function, is there a way using a For loop to pull out all the values from the DLookup, place them in an array and check the ClassID values against what's in the textbox? Revised code posted below- CODE Private Sub btnConfirm_Click()
'test Dim CkClassID As Integer 'Error handling On Error GoTo ErrorHandler1 '---Before adding student to a class roster, first check if student is already enrolled in that class 'Domain count function: check if studentID exists in tblRoster already CkClassID = DLookup("RosterClass", "tblRosters", "RosterStudent = txtStudID.value") If (CkClassID = txtClassID.Value) Then 'If both exist, let the user know and whats going to happen next MsgBox "This student has already been added to this class. Roster addition is cancelled!" 'Return focus to the Student Lookup Combobox cboStudent.SetFocus 'Remove the potential record from the fields SendKeys "{esc}", True 'Cancel all actions Cancel = True 'Exit this subroutine Exit Sub End If '---add the record and move to a new record 'set focus back to the Student Lookup cboStudent.SetFocus 'Run the query behind the subform to display the added record DoCmd.Requery 'Move to a new record DoCmd.GoToRecord acDataForm, "frmRosterAdd", acNewRec 'Error handling ExitErrorHandler1: Exit Sub 'Error handling ErrorHandler1: MsgBox Err.Description Resume ExitErrorHandler1 End Sub |
|
|
|
|
Apr 27 2012, 12:53 AM
Post
#8
|
|
|
UtterAccess Guru Posts: 857 From: Bogotá - Colombia |
First: Domain functions are slow
Second: Don't use SendKeys The code I gave you is a straightforward way of doing this. You just need to adapt it. |
|
|
|
|
Apr 27 2012, 12:57 AM
Post
#9
|
|
|
UtterAccess Enthusiast Posts: 57 |
Thanks,
Understood and will apply- any issues and I'll re-post. |
|
|
|
|
Apr 27 2012, 01:07 AM
Post
#10
|
|
|
UtterAccess Guru Posts: 857 From: Bogotá - Colombia |
In fact being very simplistic all you need is the composite index if the textboxes are bound to your table.
No code no nothing. You can still use the seek function in the before update event to track if a record already exists thou. |
|
|
|
|
Apr 27 2012, 06:08 AM
Post
#11
|
|
|
UtterAccess VIP Posts: 741 From: Dorset. UK. |
Hi adwatts,
I think that your original idea was the best way to do this and it would have worked if you had used the DCount function properly. Without seeing the rest of the code in the form it is difficult to say how the whole thing would work but the record testing part should look something like this :- If DCount("*", "tblRosters", "RosterStudent = " & Me.txtStudID & " AND RosterClass = " & Me.txtClassID) > 0 Then MsgBox "This student has already been added to this class. Roster addition is cancelled!" Else 'insert new record here!! End If As Diego says, you definitely do not want to use the SendKeys command in Access, if you need to delete a record then you can use a SQL command but this should not be necessary anyway. If the form is bound to a table then the normal method to do this sort of thing is to put the code in the Before Insert event of the form and then cancel the insert if the student has already been added to the table (it is not normally a good idea to add a record to a table and then delete it again). If the form is not bound to a table and you are using more code to insert the new record then you would just ignore the insert command if the record is already there. Peter Hibbs. |
|
|
|
|
Apr 28 2012, 10:01 AM
Post
#12
|
|
|
UtterAccess Enthusiast Posts: 57 |
Thanks for the correction on the DCount code- that worked perfectly (took out the Sendkeys action too). Although Diego's suggestion was valid, unfortunately it wouldn't have worked for this DB- it's already 7 layers deep in development and changing the PK to a CK would have had a severe cascading affect on just about everything- that table is the heart of the application.
Just FMI- why is using sendkeys a bad idea- if I'm going to convert to ACCDE, the code won't be exposed. Thanks again! |
|
|
|
|
Apr 28 2012, 10:52 AM
Post
#13
|
|
|
UtterAccess VIP Posts: 741 From: Dorset. UK. |
Hi Adwatts,
Glad to hear you sorted out your problem. The problem with SendKeys is, I think, that it is not very reliable. It is subject to timing issues and which form or application has the focus at the time it is issued and, in any case, there are usually other better methods to do what SendKeys does. Having said that, there are a few times when SendKeys can be used, when controlling external applications, for example. I have never used this command myself so maybe other users will know more. Anyway, here are a few references on other forums which you can have a look at, there are plenty more if you Google the subject. Allen Browne Tek-Tips Ask Tech All Experts Peter Hibbs. |
|
|
|
|
Apr 28 2012, 12:30 PM
Post
#14
|
|
|
UtterAccess Guru Posts: 857 From: Bogotá - Colombia |
Yeah Peter is right!
I completely forgot DCount for doing this. Glad you solved it (IMG:style_emoticons/default/thumbup.gif) This post has been edited by dipetete: Apr 28 2012, 12:31 PM |
|
|
|
|
May 2 2012, 05:40 AM
Post
#15
|
|
|
UtterAccess VIP Posts: 1,914 From: UK |
note that as well as pro-actively checking, if you add referential integrity to the relationship. you will not be able to duplicate membership anyway.
you could avoid the dcounts, just allow access to try and add the pupil, and intercept the error message. it's just a pain sometimes to have to consider and code for every "wrong" eventuality. it also depends on the interface you are aiming for. fwiw, i use sendkeys escape, only - just to simulate pressing the escape key, to undo a invalid edit. i can't believe there is a negative side to this usage. on another point, i think seek (advised earlier) will not work on linked tables. you have to use findfirst/findnext. |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 25th May 2013 - 05:24 AM |