By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

Subform ODBC Error When New Record On Parent Form

P: 31
Hi,

I have a form which contains a subform. Both are were creetd using the form wizard and are bound by the column IXO_NR (on two different tables), which is the control source for a textbox on both the parent and subform.

The intent was to default the subform's IXO_NR to the parent form's IXO_NR, then requery the subform to prevent the user from having to scroll through all of the subform's records to find the one they may be looking for. In the case of a new record on the parent form, IXO_NR on the subform should be reset since it contains the value from the last record selected.

I tried to reset it a number of ways and I kept getting an ODBC error as, I'm assuming, since a value was populated in the subform's textbox, Access interpreted that a new record was being added in the subform as well. Since the subform has a couple of not null columns, an ODBC error was generated.

On the parent form's FORM_CURRENT event:

If Me!txtInfoXchgObjNr > 0 Then
Me!subfrmIXODesc!txtInfoXchgObjNr.DefaultValue = Me!txtInfoXchgObjNr
Me!subfrmIXODesc.Requery
Else
' Me!subfrmIXODesc!txtInfoXchgObjNr.Undo
' Me!subfrmIXODesc!txtInfoXchgObjNr.Value = 0
' etc.
End If

So how can I reset the subform / subform's textbox to prevent this ?

Thanks, Ed.
May 4 '07 #1
Share this Question
Share on Google+
13 Replies


JConsulting
Expert 100+
P: 603
you're referencing your subform control incorrectly in this scenario. it should be

If Me!.xtInfoXchgObjNr > 0 Then
Me.subfrmIXODesc.form.xtInfoXchgObjNr.DefaultValue = Me.txtInfoXchgObjNr
Me.subfrmIXODesc.formRequery
Else
' Me.subfrmIXODesc.form.xtInfoXchgObjNr.Undo
' Me.subfrmIXODesc!txtInfoXchgObjNr.Value = 0
' etc.
End If

But I'm not sure if that's going to work to solve your problem. Let us know.
J
May 6 '07 #2

P: 31
you're referencing your subform control incorrectly in this scenario. it should be

Expand|Select|Wrap|Line Numbers
  1. If Me!.txtIXONr > 0 Then
  2.      Me.subfrmIXODesc.form.txtIXONr.DefaultValue = Me.txtIXONr
  3.      Me.subfrmIXODesc.formRequery
  4. Else
  5. '    Me.subfrmIXODesc.form.txtIXONr.Undo
  6. '    Me.subfrmIXODesc!txtIXONr.Value = 0
  7. '    ????
  8. End If
But I'm not sure if that's going to work to solve your problem. Let us know.
J
Hi J,

Thanks for the reply !

The code I had and the code you provided both worked as far as populating the textbox and retrieving the related records in the subform. I found that code when searching for a solution. Is you're method more efficient ?

The ODBC error is generated in the ELSE when I try to reset the textbox in the subform to a "null" value when a new record is being added and that's why it was commented out.

If I don't reset the subform's textbox when the main form is on a new record, it will be populated with an incorrect value and if data is entered on the subform and saved, it will associate the subform's data to the wrong PK.

For example, if the main form had an existing record with a PK of 2 the subform's textbox would be populated with 2 and any relevant data, if any, would be retrieved. On a new record, the main form's objects are blank but the subform's textbox contains the value of 2, instead of being blank as well.

The ODBC error occurred since the subform did not contain all of the required info for a valid insert (e.g. cannot insert a null value into columns...column does not allow nulls). If the required info was populated and saved, it'd be associated with the last PK value stored in the textbox, in this case 2.

So the question is, how do I reset the subform's textbox to a "null" value so Access does not try to insert a record and throw the ODBC error ?

As I'm sure you can tell, I'm not familiar with Access and it's objects, methods, etc. If you happen to have any references I could look into, please feel free to let me know.

Thanks again and all help is appreciated !

Ed.

P.S. Sorry for such a long reply :-)
May 7 '07 #3

JConsulting
Expert 100+
P: 603
Hi J,

Thanks for the reply !

The code I had and the code you provided both worked as far as populating the textbox and retrieving the related records in the subform. I found that code when searching for a solution. Is you're method more efficient ?

The ODBC error is generated in the ELSE when I try to reset the textbox in the subform to a "null" value when a new record is being added and that's why it was commented out.

If I don't reset the subform's textbox when the main form is on a new record, it will be populated with an incorrect value and if data is entered on the subform and saved, it will associate the subform's data to the wrong PK.

For example, if the main form had an existing record with a PK of 2 the subform's textbox would be populated with 2 and any relevant data, if any, would be retrieved. On a new record, the main form's objects are blank but the subform's textbox contains the value of 2, instead of being blank as well.

The ODBC error occurred since the subform did not contain all of the required info for a valid insert (e.g. cannot insert a null value into columns...column does not allow nulls). If the required info was populated and saved, it'd be associated with the last PK value stored in the textbox, in this case 2.

So the question is, how do I reset the subform's textbox to a "null" value so Access does not try to insert a record and throw the ODBC error ?

As I'm sure you can tell, I'm not familiar with Access and it's objects, methods, etc. If you happen to have any references I could look into, please feel free to let me know.

Thanks again and all help is appreciated !

Ed.

P.S. Sorry for such a long reply :-)

The subform record wouldn't be saved unless you actually set focus to it and enter some data. Default entries, and assumed Not Null values, even though they "appear" in the subform, aren't committed. I believe the Undo is what's causing the error..however I have no way to test that in your situation. You shouldn't have to do what you're doing.

As a test, comment out the code you have, go into your main form and enter a test record. You may see data in the subform, however...don't bother with it.

Save the Main form record.

Now go to the subform table and try to find the FK record. It will not/should not be there.

Let me know.
J
May 7 '07 #4

P: 31
The code under the ELSE was already commented out when I posted this and remains so. When I tried the UNDO, Value = 0 or setting the textbox to a variant object that had no value, I still had the problem. This only occurs when the main form is at new record and I navigate to a previous record. My guess is when the main form record position changes, which triggers the subform's requery, Access tries to insert a row on the table linked to the subform since there is a value in the textbox.

In order for me to test this theory, I need to reset the textbox to it's initial state and value. So, is there away to do that ?

Thanks, Ed.
May 7 '07 #5

JConsulting
Expert 100+
P: 603
The code under the ELSE was already commented out when I posted this and remains so. When I tried the UNDO, Value = 0 or setting the textbox to a variant object that had no value, I still had the problem. This only occurs when the main form is at new record and I navigate to a previous record. My guess is when the main form record position changes, which triggers the subform's requery, Access tries to insert a row on the table linked to the subform since there is a value in the textbox.

In order for me to test this theory, I need to reset the textbox to it's initial state and value. So, is there away to do that ?

Thanks, Ed.
If that's the case, then don't you want to Delete that record from the table? Not just reset the text box.
May 7 '07 #6

P: 31
There isn't any data to save initially.

The only thing that's populated on the subform, when the record changes on the main form, is the value in the textbox to pre-populate it with the main form's PK value. This ensures the new / modified data in the subform is linked properly as the FK and to make it easier for the user (not having to choose from a dropdown, etc. - they are already where they need to be). Think of it as a filter.

If there happens to be any data related to the main form's PK then the subform will display it and the user can modify or navigate as needed, but the PK value from the main form would remain in the subform. The subform's PK value should only change when the main form's record changes.

I know it's tough to diagnose a problem without an example. If there's a way I can send it to you, let me know. Just an FYI, the tables are links to a SQL Server database.....

Thanks, Ed.
May 7 '07 #7

JConsulting
Expert 100+
P: 603
There isn't any data to save initially.

The only thing that's populated on the subform, when the record changes on the main form, is the value in the textbox to pre-populate it with the main form's PK value. This ensures the new / modified data in the subform is linked properly as the FK and to make it easier for the user (not having to choose from a dropdown, etc. - they are already where they need to be). Think of it as a filter.

If there happens to be any data related to the main form's PK then the subform will display it and the user can modify or navigate as needed, but the PK value from the main form would remain in the subform. The subform's PK value should only change when the main form's record changes.

I know it's tough to diagnose a problem without an example. If there's a way I can send it to you, let me know. Just an FYI, the tables are links to a SQL Server database.....

Thanks, Ed.

Hi again Ed,
And I'm still not convinced this is an issue. The behavior that you're describing is normal behavior for related records. And again, I have to ask you...if you mark the record's PK value in your subform...then switch over to the subform's table...you'll see that the record has not been committed. It does show up on the form, but unless you put the cursor into the subform, and edit one of the fields in some way (effectively making Dirty = True)...then nothing of what you SEE on the form is saved to the tables.

I hope I'm not missing some important issue here...but try what I described to see if I'm mistaken.

J
May 7 '07 #8

P: 31
Hi again Ed,
And I'm still not convinced this is an issue. The behavior that you're describing is normal behavior for related records. And again, I have to ask you...if you mark the record's PK value in your subform...then switch over to the subform's table...you'll see that the record has not been committed. It does show up on the form, but unless you put the cursor into the subform, and edit one of the fields in some way (effectively making Dirty = True)...then nothing of what you SEE on the form is saved to the tables.

I hope I'm not missing some important issue here...but try what I described to see if I'm mistaken.

J
Hey J,

I have no other way to describe the behavior I'm seeing and I'm sure it's normal behavior. Even if the form was stand-alone and I tried to save a record without providing the required data, the same ODBC error would occur as described.

The problem only happens when I change the value of the subform's textbox via code in the main form (see Else).

Expand|Select|Wrap|Line Numbers
  1. If Me.txtIXONr > 0 Then
  2.     Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = Me.txtIXONr
  3.     Me.subfrmIXODesc.Form.Requery
  4. Else
  5.    Me.subfrmIXODesc.Form.txtIXONr.Value = 0  'I tried other methods as well
  6. End If
  7.  
Again, I'm not entering data manually. The subform's textbox is populated with the PK value from the main form via code to try and remove any existing value which was populated from the prior record.

So my choices seem to be to try and figure out how to reset a textbox so it contains no value and prevents the ODBC error or have the user use a dropdown and not try to reset the textbox at all. My preference is the former.

How do I reset a textbox ?

Thanks, Ed.
May 8 '07 #9

JConsulting
Expert 100+
P: 603
Hey J,

I have no other way to describe the behavior I'm seeing and I'm sure it's normal behavior. Even if the form was stand-alone and I tried to save a record without providing the required data, the same ODBC error would occur as described.

The problem only happens when I change the value of the subform's textbox via code in the main form (see Else).

Expand|Select|Wrap|Line Numbers
  1. If Me.txtIXONr > 0 Then
  2.     Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = Me.txtIXONr
  3.     Me.subfrmIXODesc.Form.Requery
  4. Else
  5.    Me.subfrmIXODesc.Form.txtIXONr.Value = 0  'I tried other methods as well
  6. End If
  7.  
Again, I'm not entering data manually. The subform's textbox is populated with the PK value from the main form via code to try and remove any existing value which was populated from the prior record.

So my choices seem to be to try and figure out how to reset a textbox so it contains no value and prevents the ODBC error or have the user use a dropdown and not try to reset the textbox at all. My preference is the former.

How do I reset a textbox ?

Thanks, Ed.

Ed,
I still think something simple is missing to cause you to have to go to all this effort.
When you placed your subform onto the main form...you linked the master/child fields right?

This is a very high level view of a proper table layout..can you confirm that you have a set-up like this?

MainTable
ID (autonumber)
Details
Details
Etc..

SubTable
ID(Autonumber)
MainTableID (Numeric Foreign Key)
Details
Details
Etc..

And you're linkiing using the Main Table ID and the Sub Table MainTableID

is that correct?
May 8 '07 #10

P: 31
Ed,
I still think something simple is missing to cause you to have to go to all this effort.
When you placed your subform onto the main form...you linked the master/child fields right?

This is a very high level view of a proper table layout..can you confirm that you have a set-up like this?

MainTable
ID (autonumber)
Details
Details
Etc..

SubTable
ID(Autonumber)
MainTableID (Numeric Foreign Key)
Details
Details
Etc..

And you're linkiing using the Main Table ID and the Sub Table MainTableID

is that correct?
The first one is correct. The second one has a PK of MainTableID (via FK) and a Sequence Number to allow more than one record for a given MainTableID.

The forms are linked on the MainTableID. And if I didn't mention this before, the forms were created via the wizard and record navigation is provided by Access' record navigation toolbar; I am not controlling those actions via code.

If you know how I can reset the textbox, let me try that first and then we can go from there.

Thanks, Ed.
May 8 '07 #11

JConsulting
Expert 100+
P: 603
The first one is correct. The second one has a PK of MainTableID (via FK) and a Sequence Number to allow more than one record for a given MainTableID.

The forms are linked on the MainTableID. And if I didn't mention this before, the forms were created via the wizard and record navigation is provided by Access' record navigation toolbar; I am not controlling those actions via code.

If you know how I can reset the textbox, let me try that first and then we can go from there.

Thanks, Ed.
Your subtable needs to be changed...and you need to link as I specified. I do not think this will work because it requires a value. That is why when you try to remove it, it errors.

Expand|Select|Wrap|Line Numbers
  1. If Me.txtIXONr > 0 Then
  2.     Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = Me.txtIXONr
  3.     Me.subfrmIXODesc.Form.Requery
  4. Else
  5.    Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = ""
  6.    Me.subfrmIXODesc.Form.Requery   
  7. End If
  8.  
May 8 '07 #12

P: 31
Your subtable needs to be changed...and you need to link as I specified. I do not think this will work because it requires a value. That is why when you try to remove it, it errors.

Expand|Select|Wrap|Line Numbers
  1. If Me.txtIXONr > 0 Then
  2.     Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = Me.txtIXONr
  3.     Me.subfrmIXODesc.Form.Requery
  4. Else
  5.    Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = ""
  6.    Me.subfrmIXODesc.Form.Requery   
  7. End If
  8.  
Hi J.,

It's not a table design issue, the tables are normalized and they have a proper FK relationship. It's a code issue, which is proven by the fact that I don't get the issue when the else code is commented out.

Let me see if I can find a workaround, otherwise I'll go back to using a combobox and have the user choose the application / system name to enter descriptions, etc. for.

I'll let you know what happens.....

Thanks again,

Ed.
May 9 '07 #13

P: 31
Hi J.,

It's not a table design issue, the tables are normalized and they have a proper FK relationship. It's a code issue, which is proven by the fact that I don't get the issue when the else code is commented out.

Let me see if I can find a workaround, otherwise I'll go back to using a combobox and have the user choose the application / system name to enter descriptions, etc. for.

I'll let you know what happens.....

Thanks again,

Ed.
Hi,

Here's the code I was given by a co-worker and it seems to have resolved it:

Private Sub Form_Current()

' Test textbox value containing the linked field (between main form frmIXF
' and subforms subfrmIXF, subfrmIXFDesc and subfrmIXFNotes). If > 0 then
' set default value to linked field, otherwise reset subform linked field value then requery.

If Me.txtHierObjRelNr > 0 Then

' refresh subform subfrmIXF

Forms![frmIXF]![subfrmIXF]![txtIXFNr].DefaultValue = Me.txtHierObjRelNr
Forms![frmIXF]![subfrmIXF]![txtIXFNr].Requery

' refresh subform subfrmIXFDesc

Forms![frmIXF]![subfrmIXFDesc]![txtIXFNr].DefaultValue = Me.txtHierObjRelNr
Forms![frmIXF]![subfrmIXFDesc]![txtIXFNr].Requery

' refresh subform subfrmIXFNotes

Forms![frmIXF]![subfrmIXFNotes]![txtIXFNr].DefaultValue = Me.txtHierObjRelNr
Forms![frmIXF]![subfrmIXFNotes]![txtIXFNr].Requery

Else

' reset subfrmIXF's textbox containing linked field value then requery

Forms![frmIXF]![subfrmIXF]![txtIXFNr].DefaultValue = ""
Forms![frmIXF]![subfrmIXF]![txtIXFNr].Requery
Forms![frmIXF]![subfrmIXF].Requery

' reset subfrmIXFDesc's textbox containing linked field value then requery

Forms![frmIXF]![subfrmIXFDesc]![txtIXFNr].DefaultValue = ""
Forms![frmIXF]![subfrmIXFDesc]![txtIXFNr].Requery
Forms![frmIXF]![subfrmIXFDesc].Requery

' reset subfrmIXFNotes's textbox containing linked field value then requery

Forms![frmIXF]![subfrmIXFNotes]![txtIXFNr].DefaultValue = ""
Forms![frmIXF]![subfrmIXFNotes]![txtIXFNr].Requery
Forms![frmIXF]![subfrmIXFNotes].Requery

End If

End Sub
Jun 1 '07 #14

Post your reply

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