Archived

This topic is now archived and is closed to further replies.

Crispy

VBA question

Recommended Posts

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

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
Im not sure, but I think you want some sql?
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.

Share this post


Link to post
Share on other sites
Mkay...

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

Share this post


Link to post
Share on other sites
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!!

Share this post


Link to post
Share on other sites
Thanks Themonkster, you led me on the right track.

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


Share this post


Link to post
Share on other sites