Issue during getting data from the access database

[sanand]

Hi,

We have an access form on which when we enter a unique number say Id and click on the button ‘Fetch’, we should get the details populated in the form from the database. Database is an Access database

Fetching will happen through a simple select query like: 'SELECT * from Test where Id = 20'. The result of the above query will give the column values that need to be assigned to the controls on the access form.

We have tried using Docmd.RunSQL but it doesn’t works for the SELECT queries.

Any suggestions please?

 

[JANR]

You could use the form's filterproperty and set it using VBA with the RecordID, like this. I assume the IDField is numeric.

Private Fetch AfterUpdate_Click()
   Me!Filter = "Id =" & Me!NameOfTextbox   ' textbox is a numberfield
   Me!FilterOn = True
End Sub

JR

 

[sanand]

Can you please elaborate a bit on code. Didnt got exactly what u meant.

 

[sanand]

I have used the code some what like:

Dim dbs as DAO.database
dim rst as DAO. Recordset

Set dbs = CurrentDB()
strsql = "select * from table where id = " & Cstr(txtid.value)
Set rst = CurrentDB.OpenRecordset(strtSql)
rst.MoveFirst

txtCreated.value = rst!Created
txtOpened.Value = rst!Opened
--
--
--
and so on

What i think is there is certain issue in the way CurrentDB has been used.
Any suggestions?

thanks

 

[JANR]

Can you please elaborate a bit on code. Didnt got exactly what u meant.

To use the filterproperty the form has to be bound to a query or table, if you use a Unbound form you could use open Recordset methode like the one you have tried. I personally like to bound all my forms to Query/Table unless the data set is very large.

Dim dbs as DAO.database
dim rst as DAO. Recordset

Set dbs = CurrentDB[COLOR=red]()  -remove these[/COLOR]
strsql = "select * from table where id = " & Cstr(txtid.value)
Set rst = CurrentDB.OpenRecordset(strtSql)
[COLOR=red]rst.MoveFirst <- you don't need to move first cause it will only be 1 record
[/COLOR]
txtCreated.value = rst!Created
txtOpened.Value = rst!Opened
--
--
--
and so on

How ever you need to test for null in txtid and also EMPTY rst, or you get an error.

Here is one template on how to do it:

Private Sub cmdFetch_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
    
'Test for Null i ID-Field
If IsNull(Me!ID) Then
    MsgBox "Choose EmpID"
    Exit Sub
Else
    
    Set db = CurrentDb
    strSql = ""
    strSql = "SELECT FirstName,MidName,LastName,DOB,HireDate,EndDate"
    strSql = strSql & " FROM tblEmployee"
    strSql = strSql & " WHERE ID =" & Me!ID
    
    Set rs = db.OpenRecordset(strSql)
    
    ' Test for empty Recordset
    If rs.BOF Then
        MsgBox " EmpID dosen't exist"
    Else
        With rs
            Me!txtFirst = rs.Fields("FirstName")
            Me!txtMiddle = rs.Fields("MidName")
            Me!txtLast = rs.Fields("LastName")
            Me!txtDOB = rs.Fields("DOB")
            Me!txtHireDate = rs.Fields("HireDate")
            Me!txtTermDate = rs.Fields("EndDate")
        End With
    End If
End If
    ' clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
End Sub

Hope this helps.

JR

 

[sanand]

Thanks so much.
It really worked...