CODEWITHSUNDEEP
ms-accessvba
Steven
Steven

Reputation: 155

Indexing a RecordSet in Access VBA to find values

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(addrPullQry)
    rst.Index = '''''''
    If rst.RecordCount > 0 Then
        MsgBox ("More than zero") 'Prompt with address info
    Else
        MsgBox ("Not more than zero") ' Assign Primary to address
    End If

addrPullQry selects all records from an Address table that has an address type of Primary (Secondary is the other option) where a Record_ID and a Group_ID I specify in the query, exist.

I am giving the user an On_Click() event which will assign an address, under a customer under a certain group, an address type. If a primary address type already exists for that customer, I need to prompt the user to overwrite it. I need to be able to find the Address values like Street, City, State, etc to print in the prompt. Those values are stored in the query as such, I just don't know how to reference them and store them in variables.

I assume rst.Index would accomplish this but I'm not familiar with it and the MSDN page wasn't very helpful.

Upvotes: 0

Views: 994

Answers (1)

RyanL
RyanL

Reputation: 1276

If I understand what you're after, you want to use the recordset you created to return address information with which to prompt the user. If that's all you need, then:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sStreet$, sCity$, sState$
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(addrPullQry)

If rst.RecordCount > 0 Then
    sStreet=rst!Street
    sCity=rst!City
    sState=rst!State
    msgbox "Customer Address: " & vbcrlf & _
        sStreet & vbcrlf & sCity & ", " & sState

Else
    MsgBox "No address on file..."
End If

Just substitute Street/City/State for the actual field names in your query 'addrPullQry'

Upvotes: 2

Related Questions