🔒 Locked

VBA question

Started by Crispy Jul 28, 2002 at 5:08 AM 4 replies 250+ views

Original Post

Jul 28, 2002 at 5:08 AM

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

Crispy

Jul 28, 2002 at 2:06 PM

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

Themonkster

Jul 28, 2002 at 3:09 PM

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!!

Crispy

Jul 29, 2002 at 4:27 AM

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

krez

Jul 29, 2002 at 6:12 PM

just put a UNIQUE INDEX on that field (it will not allow any two records to have the same value)

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.