423,688 Members | 1,889 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

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

P: 23
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub First_name_Exit(Cancel As Integer)
  4. popfullname
  5. End Sub
  6. Private Sub popfullname()
  7. 'Me.[Full name].value = Me.Last_Name.value & ", " & Me.First_name.value
  8.  
  9. End Sub
  10.  
  11.  
  12. Private Sub Form_BeforeInsert(Cancel As Integer)
  13. TrackChanges Me
  14. End Sub
  15.  
  16.  
  17.  
  18. Private Sub Last_Name_Change()
  19. Debug.Print "Working"
  20. Dim emp As String
  21. Dim intIndex As Integer
  22. Dim strSQL As String
  23. Dim DB As Database
  24. Dim rs As Recordset
  25. Set DB = CurrentDb
  26.  
  27. emp = Chr(34) & "employee = " & Forms!Staffadd.employeeid.value & Chr(34)
  28. Debug.Print emp
  29. Debug.Print Forms![Staffadd]!employeeid
  30. Debug.Print "dlookup is resolving to: "; DLookup("[containernumber]", "Access", "employee = " & Forms![Staffadd]!employeeid)
  31. Debug.Print "Numnber of active containers at this time is: "; Me.containerlist.ListCount
  32. If DLookup("[containernumber]", "Access", "employee = " & Forms![Staffadd]!employeeid) Then ' if Null then this employee has not been added to the containers access
  33.  
  34.  
  35.     Else
  36. Debug.Print "Processing continer number: ";
  37.     For intIndex = 0 To Me.containerlist.ListCount - 1
  38.         Debug.Print Me.containerlist.ItemData(intIndex);
  39.         'Open the Contact table
  40.         Set rs = DB.OpenRecordset("SELECT * FROM Access", dbOpenDynaset)
  41.  
  42.         With rs
  43.             'Set it to Add mode
  44.             .AddNew
  45.  
  46.             'Enter the field values
  47.             .Fields("employee").value = Forms![Staffadd]!employeeid
  48.             .Fields("containernumber").value = Me.containerlist.ItemData(intIndex)
  49.             .Fields("Access").value = False
  50.  
  51.             'Update it
  52.             .Update
  53.  
  54.             'Close it
  55.             .Close
  56.  
  57.         End With
  58.     Next intIndex
  59.     End If
  60. Debug.Print " "
  61. End Sub
  62.  
  63. Private Sub Last_Name_Exit(Cancel As Integer)
  64. [Container access employee add subform].Requery
  65. End Sub
  66. Private Sub selID_AfterUpdate()
  67.     ' Find the record that matches the control.
  68.     Dim rs As Object
  69.  
  70.     Set rs = Me.Recordset.Clone
  71.     rs.FindFirst "[ID] = " & Str(Nz(Me![selID], 0))
  72.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  73. End Sub
  74.  
  75.  
Jun 17 '10 #1

✓ answered by patjones

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

Share this Question
Share on Google+
17 Replies


patjones
Expert 100+
P: 931
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
Jun 18 '10 #2

P: 23
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
Jun 18 '10 #3

P: 23
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.
Jun 18 '10 #4

patjones
Expert 100+
P: 931
@lee weaver
I mean, is the form based upon one single table, or is it a SELECT statement somewhere?
Jun 18 '10 #5

P: 23
It's only based on the "Staff" Table
Jun 18 '10 #6

P: 23
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.
Jun 18 '10 #7

patjones
Expert 100+
P: 931
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
Jun 21 '10 #8

P: 23
@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.
Jun 21 '10 #9

P: 23
@zepphead80
Your observation completely eliminated teh largest section of code. Here is the remaining code. but the problem remains.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub First_name_Exit(Cancel As Integer)
  4. popfullname
  5. End Sub
  6. Private Sub popfullname()
  7. Me.[Full name].value = Me.Last_Name.value & ", " & Me.First_name.value
  8.  
  9. End Sub
  10.  
  11.  
  12. Private Sub Form_BeforeInsert(Cancel As Integer)
  13. TrackChanges Me
  14. End Sub
  15.  
  16.  
  17.  
  18.  
  19.  
  20. Private Sub Last_Name_Exit(Cancel As Integer)
  21. [Container access employee add subform].Requery
  22. End Sub
  23. Private Sub selID_AfterUpdate()
  24.     ' Find the record that matches the control.
  25.     Dim rs As Object
  26.  
  27.     Set rs = Me.Recordset.Clone
  28.     rs.FindFirst "[ID] = " & Str(Nz(Me![selID], 0))
  29.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  30. End Sub
  31.  
Jun 21 '10 #10

patjones
Expert 100+
P: 931
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
Jun 21 '10 #11

P: 23
@zepphead80
Data Entry -- Yes
Allow Additions -- Yes
Allow Deletions -- No
Allow Edits -- Yes
Jun 21 '10 #12

patjones
Expert 100+
P: 931
Set Data Entry to No and let me know what happens.
Jun 21 '10 #13

P: 23
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.
Jun 21 '10 #14

patjones
Expert 100+
P: 931
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
Jun 21 '10 #15

missinglinq
Expert 2.5K+
P: 3,532
"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)>
Jun 21 '10 #16

P: 23
@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.
Jun 21 '10 #17

P: 23
@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.
Jun 21 '10 #18

Post your reply

Sign in to post your reply or Sign up for a free account.