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

bama4954

New Member
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

Well-known Member
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

New Member
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



 
Last edited:

Micron

Well-known Member
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

New Member
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

Well-known Member
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

New Member
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.
 
Last edited:

Micron

Well-known Member
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

Well-known Member
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top