"subform datasheet view will only allow me to edit when first opened"

[bama4954]

I have added a subform to the main form so I could have some controls on the main form and the datasheet view on the subform. When I first open the main form I can edit in the subform datasheet without a problem. But if I execute the combo box "cboJobNumber" from the main form to search for a record it works fine. Once the record is found and I try to edit it I get an error: (Update or CancelUpdate without AddNew or Edit.) I have tried everything I can think of to fix it. I added Requery after update but nothing seems to work. The only way it will let me edit a record is to open and close it again.

I have all the property sheet correct with allow deletions, edits and filters set to Yes

I have it both forms linked together in the Master and Child fields by the primary key "tagID"

Any idea's?

Thanks
Tony

 

[Micron]

could be a number of things, but I'd start with asking if the combo is bound or just has a row source. If you're using it to filter subform records but you have it bound, this is incorrect. Once you make a selection you have put one form or the other (perhaps both) in edit mode. If the record selectors are visible in the form(s) see if the pencil appears. That would mean the form is in edit mode.

If that's not it then it also reads like you have code behind the form and are using a recordset. Seems like you have attempted to Update or Cancel without putting the recordset into AddNew or Edit mode. In that case, your code might shed some light on the issue.

 

[bama4954]

This is an Emulated Split Form. The Parent and Child supposedly don't need to be linked. But I have not got it to work with them linked or without.


Recordset Code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler
'This is the main form open event handler

Dim strSQL As String

strSQL = "SELECT tblTag FROM tblTag;"

Me.RecordSource = strSQL 'set the recordsource of the main form
Me.subfrmTagentry.SourceObject = "subfrmTagEntry" 'load datasheet subform w/ blank RecordSource
Set Me.subfrmTagEntry.Form.Recordset = Me.Recordset 'set subform recordset to same object as main form's

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " " & Err.Description & " in Form_Open procedure"
End Sub

***************************************************************************
Combo Box Code:

'Combobox to filter records by JobNumber
Private Sub cboFilterByJobNumber_AfterUpdate()
Me.Filter = "JobNumber = " & Forms!frmTagEntry!cboFilterByJobNumber & ""
Me.FilterOn = True
Set Me.subfrmTagEntry.Form.Recordset = Me.Recordset
cmdClearFilter.Enabled = True
cmdClearFilter.SetFocus
End Sub

 

[Micron]

Please post more than a few lines of code within code tags (# on forum toolbar).
I would have guessed that it's not possible to have a table and a field with the same name but I guess I would have been wrong. Not really a good idea. Nor do I usually have a subform and subform control with the same name.

Have to admit I've seen the emulated split form referred to somewhere but never had the desire to try the code. One thing I suspect would be an issue is that data isn't present in the form in the Open event; only in the Load event. Are you sure you can pass a recordset object to another form as its recordset object? If you set the source on one form, why not just set the same source for the other form instead?

I don't see why you have quotes here when it is supposedly a number
Forms!frmTagEntry!cboFilterByJobNumber & ""

Unfortunately you can't upload files here otherwise I might be able to find something specific.
BTW, I asked a question in the very first sentence of my reply but you have not shed any light on that. Is the combo bound or not?

 

[bama4954]

No it is not bound. I think I am going to just throw the towel in and use the regular method of having a main form and sub form done the old fashion way. The whole reason is I did not want to link the master and child tables to the sub form because it would only show one record instead of all the records. I have learned that the master and child fields do not have to be linked for the database to work properly as long as the unerlying codes and queries supports it.

But the

Emulated Split Form does indeed work flawlessly on the file I have that someone else designed. I was just trying to reproduce it to work on the database I am developing.

"Thanks you very much for your help"
​

 

[Micron]

it would only show one record instead of all the records.

2 potential reasons I can think of for that - the recordsource only returns 1 record (because of query design) or when it's a table and not a query, the filter causes only one record to be returned. To validate the latter, create a query that applies the same criteria as your filter and see if it returns only one record. If so, then only one record satisfies the query which means only one can satisfy the filter. The fact that it doesn't work as expected in this form version may have nothing to do with form settings.

 

[bama4954]

I understand what you are saying. The filter and the query are both correct. The problem is when I link the master and child to say the PK or JobNumber. It only returns one job number or one record. The user wants to be able to see all of the records on the subform datasheet and be able to use a combo box to filter the different job numbers, and be able to clear them to see all records again. The Emulated Split Form does exactly that. It acts like a form that is split but is not. I read an article today that said if you leave the main form with the row sorce blank and put all of the controls into the form header including the combo box filter that it would be able to filter whatever field criteria and see also see all records by clearing the filter. I have this in a subform now that they can do exactly that by utilizing the built in sort button. I just thought it would be cool to have a nice filter instead.

 

[Micron]

Sorry I couldn't be of more help. I figured that since you had a prior example that did work and you were trying to replicate it, there had to be something wrong with the filtering. However, if you've now moved it to a subform and still can't filter it, then I don't know what else to think besides the filter doing exactly what it's supposed to but that isn't what you expect. Filtering a subform from a main form is common. If using a combo, one has to be careful that one uses values from the correct column.
Too bad you can't upload files here...

 

[welshgasman]

removed

 

[welshgasman]

You just need a combo in the main form and a subform for that.

I use the emaulated split form, but only as a replacement for a split form and make sure the datasheet/continous is read only. I use it more for navigation.

I understand what you are saying. The filter and the query are both correct. The problem is when I link the master and child to say the PK or JobNumber. It only returns one job number or one record. The user wants to be able to see all of the records on the subform datasheet and be able to use a combo box to filter the different job numbers, and be able to clear them to see all records again. The Emulated Split Form does exactly that. It acts like a form that is split but is not. I read an article today that said if you leave the main form with the row sorce blank and put all of the controls into the form header including the combo box filter that it would be able to filter whatever field criteria and see also see all records by clearing the filter. I have this in a subform now that they can do exactly that by utilizing the built in sort button. I just thought it would be cool to have a nice filter instead.