Using Access Forms, populating all records in textbox...

[bradlannon]

Hi,

I am wondering if someone wouldn't mind taking a look at this code. I'm new to Access and VB and I cannot seem to populate my txtboxes with my table data. It only seems to load 1 record, when I click next, nothing happens. Please note: lblmax.Caption = myRecordSet.MaxRecords pulls back 0. Please help!

Option Compare Database
Option Explicit
Public mySQL As String
Public myRecordSet As New ADODB.Recordset
Public cnn1 As ADODB.Connection
Public frm As Form
Public myName As String
Public myDesc  As String
Private Sub cmdNextEmp_Click()
On Error GoTo Err_cmdNextEmp_Click
    If myRecordSet.EOF Then
        'do nothing
    Else
        myRecordSet.MoveNext
    End If
Exit_cmdNextEmp_Click:
    Exit Sub
Err_cmdNextEmp_Click:
    MsgBox Err.Description
    Resume Exit_cmdNextEmp_Click
    
End Sub
Private Sub cmdNextJob_Click()
    If myRecordSet.EOF Then
        'do nothing
    Else
        myRecordSet.MoveNext
    End If
End Sub
Private Sub cmdPrevEmp_Click()
On Error GoTo Err_cmdPrevEmp_Click
    
    If myRecordSet.BOF Then
        'beginning
    Else
         DoCmd.GoToRecord , , acPrevious 'not beginning
    End If
Exit_cmdPrevEmp_Click:
    Exit Sub
Err_cmdPrevEmp_Click:
    'MsgBox Err.Description
    Resume Exit_cmdPrevEmp_Click
    
End Sub
Private Sub cmdReport_Click()
myDesc = lbldesc.Caption
myName = lblname.Caption
MsgBox myDesc
MsgBox myName
DoCmd.OpenReport "rptEmployeeBySpecificJob", acViewPreview, , "name=' & myName & ' AND Desc=' & myDesc &'"
End Sub
Private Sub cmdTest_Click()
End Sub
Private Sub Form_Load()
Set frm = Forms("frmEmployeeSpecificJob")
populateMyRecords
End Sub
Public Function populateMyRecords()
Dim strSelectedName As Integer
Set cnn1 = CurrentProject.Connection
myRecordSet.ActiveConnection = cnn1
'mySQL = "SELECT Jobs.Desc, Employees.name FROM Jobs, Employees"
mySQL = "SELECT Jobs.*, Employees.*, HoursEntry.jobID, HoursEntry.empID, HoursEntry.date, HoursEntry.hours, HoursEntry.travelHours FROM Jobs INNER JOIN (Employees INNER JOIN HoursEntry ON Employees.empID = HoursEntry.empID) ON Jobs.jobID = HoursEntry.jobID"
myRecordSet.Open mySQL
    If myRecordSet.RecordCount > 0 Then
        myRecordSet.MoveFirst
    Do While Not myRecordSet.EOF
        MsgBox myRecordSet![name]
        MsgBox myRecordSet![Desc]
        myRecordSet.MoveNext
    Loop
    Else
    MsgBox ("must be no records i guess")
    End If
lblname.Caption = myRecordSet![name]
lbldesc.Caption = myRecordSet![Desc]
lblmax.Caption = myRecordSet.MaxRecords
myRecordSet.MoveFirst
DoCmd.SetWarnings False
'Set myRecordSet = Nothing

End Function
Private Sub cmdPrevJobs_Click()
On Error GoTo Err_cmdPrevJobs_Click
  If myRecordSet.BOF Then
        'beginning
    Else
         DoCmd.GoToRecord , , acPrevious 'not beginning
    End If
Exit_cmdPrevJobs_Click:
    Exit Sub
Err_cmdPrevJobs_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrevJobs_Click
    
End Sub

 

[jzwp22]

Welcome to AWF!

I'm a little confused; could you explain what your code is designed to do? Are you trying to run the code from a command button on a specific form? I see both functions and procedures in the code you provided in your post, so I'm not sure which code you are referring to.

 

[bradlannon]

Hi...sorry for the confusion. Basically what I have is 3 tables (Employees, HoursEntry, Jobs) and I am trying to pull a report (by clicking on CmdReport button) based on Jobs.Desc and Employee.name that are populated on the form. (I am using Access Forms as front end and it automatically loads the Employees.name field through the form's control source. Then I have coded a connection and recordset to pull records where the HoursEntry.EmpID (fk) is equal to the Employee ID record that was automatically loaded).

This may sound more confusing than it actually is...but hopefully it was at least a little clear. Thanks for your time!

 

[jzwp22]

Do you have the report set up?

If you want a report just for the record that is currently displayed on your form, then the code for the button would be along these lines. This assumes that the primary key is present in the form control pkEmpID and the field name has the same name:

 Dim stDocName As String
    stDocName = "frmEmployees"
    DoCmd.OpenReport stDocName, acPreview, , "pkempID=" & Me.pkEmpID

You mentioned that you want to base the selection on the employee name, so you would have to modify the code to compensate for a text field (employee name) rather than a numeric field (pkEmpID). A text field value has to be bounded by single quotes

 Dim stDocName As String
    stDocName = "frmEmployees"
    DoCmd.OpenReport stDocName, acPreview, , "[employeenamefieldname]=[COLOR="Red"]'[/COLOR]" & Me.employeenamecontrolname [COLOR="red"]& "'"[/COLOR]

It sounds like the job description is in a related table Jobs. This implies that an employee can hold (or could have held) many jobs throughout their career with the company. How do you currently show the job description on your form, in a subform? How is the user going to select the correct job description? What is the actual table structure of your three tables?

 

[bradlannon]

I have the report set up, I am fairly confident that everything is ok with that. Actually my problem originates before I even have the chance to click on the cmdReport button. Here is what I've noticed...

When I put a MsgBox myRecordSetDesc.RecordCount it always returns a -1 for some reason. It's something to do with returning values from recordset.

I also show the Jobs.Desc in a text field (like Employees.empID and Employees.name) and from the records in those textboxes. The Employees.empID and .name are automatically loaded (using access controls) but I specifically take the value in Employee.empID, match it in HoursEntry to pull related records.

Here is my tables:

Employees
EmpID pk
name
retired

HoursEntry
hoursEntryID
JobID fk
EmpID fk
date
hours
travelhours

Jobs
JobID pk
JobNum
InvoiceNum
PONum
Desc

I've also noticed that the select statement is correctly pulling

mySQLDesc = "SELECT Jobs.*, HoursEntry.* FROM Jobs, HoursEntry WHERE HoursEntry.empID = " & txtEmpID.Text

Here is my full code at the moment for that form...note: I am not using any modules or anything like that.

Option Compare Database
Option Explicit
Public myRecordSetDesc As New ADODB.Recordset
Public mySQLDesc As String
Public cnn1 As ADODB.Connection
Public frm As Form
Public myNameID As Integer
Public myDesc  As String
 
 
 
Private Sub cmdReport_Click()
'myDesc = "Scotsburn"
'myName = "Henry"
'myDesc = lbldesc.Caption
'myName = lblname.Caption
MsgBox myDesc
MsgBox Me.myNameID
DoCmd.OpenReport "rptEmployeeBySpecificJob", acViewPreview, , "name='" & Me.myNameID & "' AND Desc=' & myDesc &'"
End Sub
 
Private Sub cmdTest_Click()
populateMyRecords
End Sub
 
Private Sub Form_Load()
Set frm = Forms("frmEmployeeSpecificJob")
populateMyRecords
End Sub
 
 
Public Function populateMyRecords()    'should I create a recordset for 2 then match by ID by a hidden field?  YES!
Dim strSelectedName As Integer
Set cnn1 = CurrentProject.Connection
myRecordSetDesc.ActiveConnection = cnn1
txtEmpID.SetFocus
myNameID = txtEmpID.Value
'mySQL = "SELECT Jobs.Desc, Employees.name FROM Jobs, Employees"
mySQLDesc = "SELECT Jobs.*, HoursEntry.* FROM Jobs, HoursEntry WHERE HoursEntry.empID = " & txtEmpID.Text
'will have to do inner join so I can reference"
' may have to use this example =DLookUp "Rate","TBL_Labor","Routing='F30'") sometime
myRecordSetDesc.Open mySQLDesc
'myRecordSetDesc.MoveLast
MsgBox myRecordSetDesc.RecordCount
'Do While Not myRecordSetDesc.EOF
'myRecordSetDesc.MoveNext
'MsgBox myRecordSetDesc.AbsolutePosition
'Loop
lblmax.Caption = myRecordSetDesc.RecordCount
myRecordSetDesc.MoveFirst
lbldesc.Caption = myRecordSetDesc![Desc]
 
DoCmd.SetWarnings False
'Set myRecordSet = Nothing
 
End Function
Private Sub cmdPrevJobs_Click()
On Error GoTo Err_cmdPrevJobs_Click
  If myRecordSet.BOF Then
        'beginning
    Else
         DoCmd.GoToRecord , , acPrevious 'not beginning
    End If
Exit_cmdPrevJobs_Click:
    Exit Sub
Err_cmdPrevJobs_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrevJobs_Click
 
End Sub

 

[jzwp22]

I am confused by your code. Are your tables located within Access? If so just create the query that gives you the data you want and then create a form based on that query. There would be no need for code to populate the records in the form since your form would be bound to the recordset created by the query. You can then add the command button to that form.

I usually use the following to test to see if a recordset has records which is a little different from what you are doing

myrecset1.Open mySQL1
If myrecset1.BOF And myrecset1.EOF Then
    MsgBox "Invalid Lab #, Try again"
    myrecset1.Close
Else
   Do until myrecset1.EOF
    run your code
   myrecset1.MoveNext
   Loop 
End If

Would it be possible for you to post your database with any sensitive data removed?

 

[bradlannon]

Hi, I've decided to go another route with this. As you can see from my attached timeclock.mdb, I am working on form frmEmployeeSpecificJob. Basically what I'm trying to do is first load txtname (through access forms control source....not in vb code). When a particular record (in forms 'txtname' textfield) is displayed (from table Employees.name field) the combo box (comboJobs) automatically updates to show only the "Desc" field in the Jobs.Desc tablefield.

So far I have the txtname populating and then a function is loaded to get the Desc value. This sort of does work but only 1 value is added to the list. I am understanding that I cannot use .addvalue as I am using Access 2000. I am looking to click on comboJobs, and have the records displayed vertically (currently I can only make them horizontally which messes up my value needed to open report)

Here is my code for that form...

Public strSelectedName As Integer
Public mySQLDesc As String
Public cnn1 As ADODB.Connection
Public myDesc As String
Public xcombo As Object
Public myRecordSetDesc As New ADODB.Recordset
'-----------------------------------------------------'
Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click
'Form_frmEmployeeSpecificJob.comboJobs.SetFocus
Form_frmEmployeeSpecificJob.txtname.SetFocus
DoCmd.OpenReport "rptEmployeeBySpecificJob", acViewPreview, , "name='" & Form_frmEmployeeSpecificJob.txtname.Text & "' AND Desc='" & Form_frmEmployeeSpecificJob.comboJobs & "'"
'Form_frmEmployeeSpecificJob.Form.
Form_frmEmployeeSpecificJob.Form.Visible = False
'Unload [Form_frmEmployeeSpecificJob]
Exit_cmdReport_Click:
    Exit Sub
Err_cmdReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdReport_Click
End Sub
Public Function populateMyRecords()
Set cnn1 = CurrentProject.Connection
 
myRecordSetDesc.ActiveConnection = cnn1  'maybe write an if statement to determine if connection/recordset is open..
txtEmpID.SetFocus
'mySQLDesc = "SELECT Jobs.*, HoursEntry.* FROM Jobs INNER JOIN HoursEntry ON Jobs.jobID=HoursEntry.jobID WHERE HoursEntry.empID = " & txtEmpID.Text
mySQLDesc = "SELECT distinct Jobs.Desc FROM Jobs, HoursEntry WHERE HoursEntry.empID = " & txtEmpID.Text
myRecordSetDesc.Open mySQLDesc
'was in combojobs rowsource
'SELECT Jobs.Desc FROM Jobs INNER JOIN (Employees INNER JOIN HoursEntry ON Employees.empID = HoursEntry.empID) ON Jobs.jobID = HoursEntry.jobID WHERE (((Employees.name)=[Forms]![frmEmployeeSpecificJob]![txtname])) ORDER BY Jobs.Desc;
    Do While Not myRecordSetDesc.EOF
         myRecordSetDesc.MoveNext
        If myRecordSetDesc.EOF Then
            'do nothing
         Else
 
            Form_frmEmployeeSpecificJob.comboJobs.Value = myRecordSetDesc![Desc]
 
        End If
    Loop
DoCmd.SetWarnings False
'Set myRecordSetDesc = Nothing
End Function
Private Sub cmdPreviousEmp_Click()
On Error GoTo Err_cmdPreviousEmp_Click
'if recordset is at the start .bof then do nothing, else docmd.gotorecord
If myRecordSetDesc.BOF = True Then
MsgBox ("next: beginning of records")
Else
    DoCmd.GoToRecord , , acPrevious
    populateMyRecords
End If
'Set myRecordSetDesc = Nothing
Exit_cmdPreviousEmp_Click:
    Exit Sub
Err_cmdPreviousEmp_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviousEmp_Click
 
End Sub
Private Sub cmdNextEmp_Click()
On Error GoTo Err_cmdNextEmp_Click
'if recordset is at the end .eof then do nothing, else docmd.gotorecord
If myRecordSetDesc.EOF = True Then
MsgBox ("next: end of records")
Else
    DoCmd.GoToRecord , , acNext
    populateMyRecords
End If
'Set myRecordSetDesc = Nothing
Exit_cmdNextEmp_Click:
    Exit Sub
Err_cmdNextEmp_Click:
    MsgBox Err.Description
    Resume Exit_cmdNextEmp_Click
 
End Sub
Private Sub Form_Load()
populateMyRecords
End Sub

Note: I can print the correct report when just one record is displayed in comboJobs...obviously doesn't work when I try to concatenate records (as they only show up horizontally)

Thank you for your help and for your time!!!


Tables
-------

Employees
EmpID pk
name NEEDS TO DISPLAY THIS RECORD
retired

HoursEntry
hoursEntryID
JobID fk
EmpID fk
date
hours
travelhours

Jobs
JobID pk
JobNum
InvoiceNum
PONum
Desc NEEDS TO DISPLAY THIS RECORD


Sincerely,
Brad

 

[jzwp22]

I simplified the code in your form. All the changes described below are in the attached database. This is a summary of what I did:

1. Changed the record source of the form to this query:

SELECT Employees.name, Jobs.JobNum, Jobs.Desc, HoursEntry.Date, HoursEntry.hours, HoursEntry.travelHours, Employees.empID, HoursEntry.jobID
FROM Jobs INNER JOIN (Employees RIGHT JOIN HoursEntry ON Employees.empID = HoursEntry.empID) ON Jobs.jobID = HoursEntry.jobID;

2. Added a textbox to the form to hold the empID

3. Added this to the row source of the combo box
SELECT DISTINCT qryComboBox.jobID, qryComboBox.Desc, qryComboBox.empID
FROM qryComboBox
WHERE (((qryComboBox.empID)=[forms]![frmEmployeeSpecificJobv2]![empID]));


4. Added this code to the on current event of the form:

Private Sub Form_Current()
Me.cboJob.Requery
End Sub

5. Modified your code for the report button on the form to this:

DoCmd.OpenReport "rptEmployeeBySpecificJob", acViewPreview, , "empID=" & Me.empID & " AND jobID=" & Me.cboJob
Form_frmEmployeeSpecificJob.Form.Visible = False

6. Removed all other code for recordsets, populatemyrecords etc.

7. Used the error handling of the Previous and Next record buttons to take care of when you are at the first or last record of the form's recordsource.

Private Sub cmdPreviousEmp_Click()
On Error GoTo Err_cmdPreviousEmp_Click
    Me.cboJob = Null
    DoCmd.GoToRecord , , acPrevious
Exit_cmdPreviousEmp_Click:
    Exit Sub

Err_cmdPreviousEmp_Click:
    MsgBox "You are at the first record"
    Resume Exit_cmdPreviousEmp_Click
    
End Sub

and

Private Sub cmdNextEmp_Click()
On Error GoTo Err_cmdNextEmp_Click
    Me.cboJob = Null
    DoCmd.GoToRecord , , acNext
Exit_cmdNextEmp_Click:
    Exit Sub

Err_cmdNextEmp_Click:
    MsgBox "You are at the last record"
    Resume Exit_cmdNextEmp_Click
    
End Sub

8. Amended the query underlying the report to this:

SELECT Employees.name, Jobs.JobNum, Jobs.Desc, HoursEntry.Date, HoursEntry.hours, HoursEntry.travelHours, Employees.empID, HoursEntry.jobID
FROM Jobs INNER JOIN (Employees RIGHT JOIN HoursEntry ON Employees.empID = HoursEntry.empID) ON Jobs.jobID = HoursEntry.jobID;