Form will only display data if the form property "Record set type" is set to Snapshot

[lee weaver]

I have a form to edit employee data that is a copy of the add employee form ( Which works great) with a list box at the top to select the employee to edit that is susposed to navigate to the correct record.

I am using the list box wizard to create the listbox and select the record. The problem is that it only works if i set the form to snapshot so I then can not edit the data.

It will navigate correctly and display the correct data if I have Snapshot sleected at the Record set type. If I have use "Dyanset" it displays the listbox. but nothing else on my form.

What am I doing wrong?

here is the code for the form if it helps.

Option Compare Database
 
Private Sub First_name_Exit(Cancel As Integer)
popfullname
End Sub
Private Sub popfullname()
'Me.[Full name].value = Me.Last_Name.value & ", " & Me.First_name.value
 
End Sub
 
 
Private Sub Form_BeforeInsert(Cancel As Integer)
TrackChanges Me
End Sub
 
 
 
Private Sub Last_Name_Change()
Debug.Print "Working"
Dim emp As String
Dim intIndex As Integer
Dim strSQL As String
Dim DB As Database
Dim rs As Recordset
Set DB = CurrentDb
 
emp = Chr(34) & "employee = " & Forms!Staffadd.employeeid.value & Chr(34)
Debug.Print emp
Debug.Print Forms![Staffadd]!employeeid
Debug.Print "dlookup is resolving to: "; DLookup("[containernumber]", "Access", "employee = " & Forms![Staffadd]!employeeid)
Debug.Print "Numnber of active containers at this time is: "; Me.containerlist.ListCount
If DLookup("[containernumber]", "Access", "employee = " & Forms![Staffadd]!employeeid) Then ' if Null then this employee has not been added to the containers access
 
 
    Else
Debug.Print "Processing continer number: ";
    For intIndex = 0 To Me.containerlist.ListCount - 1
        Debug.Print Me.containerlist.ItemData(intIndex);
        'Open the Contact table
        Set rs = DB.OpenRecordset("SELECT * FROM Access", dbOpenDynaset)
 
        With rs
            'Set it to Add mode
            .AddNew
 
            'Enter the field values
            .Fields("employee").value = Forms![Staffadd]!employeeid
            .Fields("containernumber").value = Me.containerlist.ItemData(intIndex)
            .Fields("Access").value = False
 
            'Update it
            .Update
 
            'Close it
            .Close
 
        End With
    Next intIndex
    End If
Debug.Print " "
End Sub
 
Private Sub Last_Name_Exit(Cancel As Integer)
[Container access employee add subform].Requery
End Sub
Private Sub selID_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![selID], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
 

[patjones]

What does the recordset that the form is based upon look like?

I'm also wondering why you use Dim rs As Object to declare the recordset rather than Dim rs As Recordset in the AfterUpdate procedure for the combo box. I don't know that this would make any difference, but it's just something I noticed.

Pat

[lee weaver]

The Dim rs as Object came from acccess when I created the list box with the wizard and told it to select the record in the table based on the selection made. It's auto generated code. the record set is as follows.
ID - AutoNumber
LastName - Text
FirstName - Text
Organization - Text
Work Phone - Text
Home phone - Text
Full name - Text
Active - Yes/No

[lee weaver]

I just tried Changing to Dim rs As Recordset with no luck. Same results. No data is populated if recordset type property in the form properties is set to anything but Snapshot.

[patjones]

@lee weaver
I mean, is the form based upon one single table, or is it a SELECT statement somewhere?

[lee weaver]

It's only based on the "Staff" Table

[lee weaver]

There is a sub form that is also not displaying corectly that is based on another table.

But that works fine in the Add employee routine. all i did for this edit form was add this list box at the top to select the employee's record to edit.

[patjones]

Hi,

I continued to think about your problem on and off over the course of the weekend, and I confess that I'm at a loss as far as giving you a clear answer for why this is happening; yet, when I look over our discussion so far, I feel like somehow I do know what is going on here. This is very much like something being on the tip of one's tongue but not being able to get it out.

Something I thought of is to ask you more about the code you posted in the first post. You mentioned that this form is a copy of the form you do the data entry in, which I have no problem with, necessarily; but it occurs to me now to ask you what the purpose of that code is and whether you intend for it to work with the data entry version of the form, or the look-up version that you are currently having problems with?

Pat

[lee weaver]

@zepphead80
At first I was thinking that I would need all of this code. But after thinking this over, there is a large section I do not ned. will make apporate changes. but I'm almost certain this isn't causing my issue. but one never knows until one trys.

Thanks for the input.

[lee weaver]

@zepphead80
Your observation completely eliminated teh largest section of code. Here is the remaining code. but the problem remains.

Option Compare Database
 
Private Sub First_name_Exit(Cancel As Integer)
popfullname
End Sub
Private Sub popfullname()
Me.[Full name].value = Me.Last_Name.value & ", " & Me.First_name.value
 
End Sub
 
 
Private Sub Form_BeforeInsert(Cancel As Integer)
TrackChanges Me
End Sub
 
 
 
 
 
Private Sub Last_Name_Exit(Cancel As Integer)
[Container access employee add subform].Requery
End Sub
Private Sub selID_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![selID], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 

[patjones]

Can you tell me what you have for the following settings in the main form's property sheet?

1. Data Entry
2. Allow Additions
3. Allow Deletions
4. Allow Edits

[lee weaver]

@zepphead80
Data Entry -- Yes
Allow Additions -- Yes
Allow Deletions -- No
Allow Edits -- Yes

[patjones]

Set Data Entry to No and let me know what happens.

[lee weaver]

You my friend are a genius. Thank you so Very much.

How counter intuitive is that? I figured, I need to be able to enter information so i need that to yes. Wow thanks again Microsoft.

[patjones]

Great. I was focusing on the code, but when I mimicked your situation in my test database and looked at these settings, I found our answer. It took me a while because I do not often work with bound forms.

When Data Entry is set to "Yes", the form opens with all the boxes blank in order to accommodate the entry of new records; when it is set to "No", this is essentially the look-up mode.

This could be one way that you'd use the same form, rather than two different copies of the same form, for both data entry and data look-up. As I mentioned, I am not a heavy user of bound forms, so I don't know if this is proper design. In my work projects, I mostly use unbound forms which can serve both entry and edit functions; for an unbound form, those four settings don't make a difference.

One other thing I would note is that your On Exit subs in your code might not produce quite the results that you are looking for. I see that you want to bring together the first and last names in another text box, which is fine; but when I did this in my test db I found that what you put in the unbound text box lags the form's recordsource by one record...

Pat

[missinglinq]

"When Data Entry is set to "Yes", the form opens with all the boxes blank in order to accommodate the entry of new records"

It should also be added that Data Entry = Yes means that you can only add new records!

Data Entry = No but AllowAdditions = Yes means that you can add new records but still view/edit (if AllowEdits = Yes) existing records.

And yes, the Data Entry name for the Property is misleading! Should be Add New Records Only or some such!

Linq ;0)>

[lee weaver]

@zepphead80
I am attempting to do away with the other field as we speak. It was a way I came up with getting around my lack of knowledge about multi column list boxes. but is has been working well for me. not sure why it wsan't working for you. it's going to take a bit of rework now to get the 2 fields that I have 1 in this table and 1 in the containers field removed and teh code adjusted but it's teh right thing to do. thanks again for the help.

[lee weaver]

@missinglinq
Linq:

Thanks for the quick turtorial on those properties. I have 3 count them 3 books here on VBA and access not 1 addresses this.