Recordset Question

[DSTR3]

I have this Recordset thing that is working but, being new to this I' thinking that this isn't exactly coded correctly. All of the fields are in the same record so I don't need to move to another record so tried taking out the Loop and MoveNext but it doesn't work. How do I code this correctly?
Thanks

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Customers WHERE Customers.CustomerID = " & Me.TxtCusID & "", dbOpenDynaset)
With rst
Do Until .EOF
Me.TxtBusinessName = !BusinessName
Me.TxtDeeName = !FName & " " & !LName
Me.TxtAddress = !Address
Me.TxtApt = !Apt
Me.TxtCity = !City
Me.TxtState = !State
Me.TxtZipCode = !ZipCode
Me.TxtTel = !Tel
Me.TxtExt = !Ext
Me.TxtFax = !Fax
Me.TxtEMail = !EMail
Me.TxtCreditLimit = !CreditLimit
Me.ChkHouseAccount = !HouseAccount
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[rjoubert]

Why are you taking the loop and movenext statement out of the code? Are you assuming that only one record is in this recordset?

If so, before accessing the field values in the recordset, try using rst.MoveFirst.

 

[DSTR3]

Yes there is only one record for that customer i the customer table. So do I also take the Loop Out?
Thanks
DS

 

[DSTR3]

I tried it and it freezes up.
DS

 

[rjoubert]

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Customers WHERE Customers.CustomerID = " & Me.TxtCusID & "", dbOpenDynaset)
With rst
.MoveFirst
Me.TxtBusinessName = !BusinessName
Me.TxtDeeName = !FName & " " & !LName
Me.TxtAddress = !Address
Me.TxtApt = !Apt
Me.TxtCity = !City
Me.TxtState = !State
Me.TxtZipCode = !ZipCode
Me.TxtTel = !Tel
Me.TxtExt = !Ext
Me.TxtFax = !Fax
Me.TxtEMail = !EMail
Me.TxtCreditLimit = !CreditLimit
Me.ChkHouseAccount = !HouseAccount
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[DSTR3]

Thanks! That works!
DS

 

[PHV]

Another way:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Customers WHERE CustomerID=" & Me!TxtCusID, dbOpenDynaset)
With rst
If Not (.BOF Or .EOF) Then
Me!TxtBusinessName = !BusinessName
Me!TxtDeeName = !FName & " " & !LName
Me!TxtAddress = !Address
Me!TxtApt = !Apt
Me!TxtCity = !City
Me!TxtState = !State
Me!TxtZipCode = !ZipCode
Me!TxtTel = !Tel
Me!TxtExt = !Ext
Me!TxtFax = !Fax
Me!TxtEMail = !EMail
Me!TxtCreditLimit = !CreditLimit
Me!ChkHouseAccount = !HouseAccount
Else
MsgBox Me!TxtCusID & " : Not found"
End If
.Close
End With
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DSTR3]

Even Better!
Thanks
DS

 

[TheAceMan1]

How are ya DSTR3 . . .
[ol][li]When you open a recordset its [blue]automatically set to the 1st record[/blue].[/li]
[li]You just need to check if an empty recordset is returned!.[/li]
[li]For the following code add a checkmark [blue]?[/blue] to the [blue]Tag[/blue] property of the fields of interest:

[blue]   Dim dbs As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, ctl As Control, Name As String
   
   Set dbs = CurrentDb
   SQL = "SELECT * " & _
         "FROM Customers " & _
         "WHERE (CustomerID = " & Me.TxtCusID & ");"
   Set rst = dbs.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then [green]'check for empty recordset[/green]
      For Each ctl In Me.Controls
         Name = ctl.Name
         
         If Name = "FName" Then
            Me.TxtDeeName = rst!FName & " " & rst!LName
         ElseIf (ctl.Tag = "[purple][b]?[/b][/purple]") And (Name <> "LName") Then
            Me("Txt" & Name) = rst(Name)
         End If
      Next
   End If
   
   Set rst = Nothing
   Set dbs = Nothing[/blue]

[/li][/ol]

See Ya! . . . . . .

 

[DSTR3]

Thanks, I'll give it a shot!
DSTr3