Recordset to fill text boxes 1

[icewiper]

Hello,

I am new to recordsets and need a little help if you would. I am tring to populate a form with a recordset with unbound text boxes and i am getting a
"run-time error '3265' Item not found in this colection" I have tried several different ways but nothing is working for me.
I have my code posted below

Private Sub Form_Load()

Dim ddbase As Database
Dim rs As Object

sql = "SELECT tblDetails.EmpID, tblMainEmp.LastName, tblMainEmp.FirstName, "
sql = sql & "tblMainEmp.MI, tblMainEmp.PermSiteLoc, tblDetails.AwardType, tblDetails.Amount, "
sql = sql & "tblDetails.Notes, tblDetails.DateNominated "
sql = sql & "FROM tblDetails LEFT JOIN tblMainEmp on tblDetails.EmpID = tblMainEmp.EmpID;"

    Set ddbase = CurrentDb
    Set rs = ddbase.OpenRecordset(sql, dbOpenSnapshot)

rs.MoveLast
rs.MoveFirst

Me.txtEmpID.Text = rs.Fields("tblDetails.EmpID").Value
Me.txtLName.Text = rs.Fields("tblMainEmp.LastName").Value
Me.txtFName.Text = rs.Fields("tblMainEmp.FirstName").Value
Me.txtMI.Text = rs.Fields("tblMainEmp.MI").Value
Me.txtSite.Text = rs.Fields("tblmainEmp.PermSiteLoc").Value
Me.txtAward.Text = rs.Fields("tblDetails.AwardType").Value
Me.txtAmount.Text = rs.Fields("tblDetails.Amount").Value
Me.txtNotes.Text = rs.Fields("tblDetails.Notes").Value
Me.txtDateNominated.Text = rs.Fields("tblDetails.DateNominated").Value

    Set rs = Nothing
    Set ddbase = Nothing

 End Sub

thank you

 

[fneily]

Did you set a reference to the DAO library? While in VBA, click Tools - References and make sure the DAO library is new the top. If not, move it there. Also, to make sure, you can add
Dim ddbase As DAO.Database
Dim rs As DAO.Recordset (Not Object)

Also add
rs.Close
ddbase.Close
before your Set rs = nothing

give that a try. And, of course check all your spelling.

 

[icewiper]

I checked and the reference is there. I did the changes as you suggested and still the same error. I have used this code many times but only pulling from one table. I should have mentioned I am trying to pull from 2 different tables. Also this is the first time i am trying to populate fields. I usually just use if for gathering the information to check data.

 

[fneily]

You also forgot to mention tblMainEmp in your SQL FROM statement. So From tblMainEmp tblDetails

 

[fneily]

Forgot a comma -
From tblMainEmp, tblDetails

 

[icewiper]

thanks fneily i will try that and see. but beleive it or not i got it working. there were 3 issues (1) i didn't dim the sql as a string (2) I had to set the focus each time for each box (3) I had to remove the table name from each rs.field and only leave the field names.

here is what i have

Private Sub Form_Load()

Dim ddbase As Database
Dim rs As Object
Dim sql As String

sql = "SELECT tblDetails.EmpID, tblMainEmp.LastName, tblMainEmp.FirstName, "
sql = sql & "tblMainEmp.MI, tblMainEmp.PermSiteLoc, tblDetails.AwardType, tblDetails.Amount, "
sql = sql & "tblDetails.Notes, tblDetails.DateNominated "
sql = sql & "FROM tblDetails LEFT JOIN tblMainEmp on tblDetails.EmpID = tblMainEmp.EmpID;"

    Set ddbase = CurrentDb
    Set rs = ddbase.OpenRecordset(sql, dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst

Me.txtEmpID.SetFocus
Me.txtEmpID.Text = rs.Fields("EmpID").Value
Me.txtLName.SetFocus
Me.txtLName.Text = rs.Fields("LastName").Value
Me.txtFName.SetFocus
Me.txtFName.Text = rs.Fields("FirstName").Value
Me.txtMI.SetFocus
Me.txtMI.Text = rs.Fields("MI").Value
Me.txtSite.SetFocus
Me.txtSite.Text = rs.Fields("PermSiteLoc").Value
Me.txtAward.SetFocus
Me.txtAward.Text = rs.Fields("AwardType").Value
Me.txtAmount.SetFocus
Me.txtAmount.Text = rs.Fields("Amount").Value
Me.txtNotes.SetFocus
Me.txtNotes.Text = rs.Fields("Notes").Value
Me.txtDateNominated.SetFocus
Me.txtDateNominated.Text = rs.Fields("DateNominated").Value

    rs.Close
ddbase.Close
    Set rs = Nothing
    Set ddbase = Nothing

 End Sub

But what i was hoping for was to make it look like a datasheet. To actually have it show all the informations of each row.

any ideas?

and thank you again for helping and the very fast response

 

[fneily]

Didn't notice the absent Dim statement, I never had to Setfocus, didn't look through your rs. statements. It's Sunday morning here.
Do you mean you want your form to be a continuous form? On the forms property sheet, change default view to Continuous Forms.

 

[icewiper]

i have it on continuous form and it only shows the first record. not all of them. I know i can do it in the record source. but i really would like to do it with record sets.

that ok, its sunday evening here. I had my morning fun too.

 

[PHV]

To avoid the SetFocus calls:
Me!txtEmpID.[!]Value[/!] = rs.Fields("EmpID").Value
Me!txtLName.[!]Value[/!] = rs.Fields("LastName").Value
...
Me!txtDateNominated.[!]Value[/!] = rs.Fields("DateNominated").Value

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

 

[icewiper]

PHV thank you, that works great. It will save me alot of time while coding.