VBA question
Author
Hi,
I need to check if a record with any given characteristics already exists in the Access database. How should I go about that (what function to use?). Here''s the specific case:
The user adds a new entry with Month = 2, Year = 2002
Now these cannot overlap on any two records, but since I don''t know how to or if it is at all possible to create a two-field unique id for a table, I''m sticking with VBA code
So I need to conduct a find in the table for any other records with Month = 2 and Year = 2002. How can I do that?
Thanks in advance,
Crispy
Im not sure, but I think you want some sql?
anyway:
anyway just check if you get anything with EOF (i.e. if first record is EOF then you didnt) and your done.
anyway:
SELECT * WHERE month = 2 AND year = 2002 anyway just check if you get anything with EOF (i.e. if first record is EOF then you didnt) and your done.
Author
Mkay...
So I select stuff from the database by calling:
Now, I''m quite useless all by myself when in comes to coding in VB because of some serious lack of experience, so I can''t figure out how to store what db.Execute returns in some variable. var = db.Execute strSQL won''t work... Furthermore, I have no idea as to how to iterate through the returned records. I''d appreciate some more specific pointers if possible...
Crispy
So I select stuff from the database by calling:
Dim db As Database Dim strSQL As String strSQL = "SELECT * FROM MyTable WHERE Year = Me!Year AND MonthID = Me!Month" Set db = CurrentDb db.Execute strSQL Now, I''m quite useless all by myself when in comes to coding in VB because of some serious lack of experience, so I can''t figure out how to store what db.Execute returns in some variable. var = db.Execute strSQL won''t work... Furthermore, I have no idea as to how to iterate through the returned records. I''d appreciate some more specific pointers if possible...
Crispy
to search on the net or visit vbcode.com
in short I work with access95 because the company i work for are cheap.
I think you are missing this
Dim rst As Recordset
then sonething like this
Set rst = strSQL
should get you going in the right direction!!
in short I work with access95 because the company i work for are cheap.
I think you are missing this
Dim rst As Recordset
then sonething like this
Set rst = strSQL
should get you going in the right direction!!
Author
Thanks Themonkster, you led me on the right track.
Here''s my solution if it''s of any use to anyone:
Here''s my solution if it''s of any use to anyone:
Dim rst As DAO.Recordset Set rst = Me.RecordsetClone Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable WHERE [Year] = " & _ Me!CurrentYear.Value & " AND [Month] = " & Me!CurrentMonth.Value & "", dbOpenDynaset) If rst.RecordCount > 1 Then MsgBox "The record exists!" End If rst.Close
just put a UNIQUE INDEX on that field (it will not allow any two records to have the same value)
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
