Subform ODBC Error When New Record On Parent Form

[bitsnbytes64]

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!txtInfoXchgO bjNr > 0 Then
Me!subfrmIXODes c!txtInfoXchgOb jNr.DefaultValu e = Me!txtInfoXchgO bjNr
Me!subfrmIXODes c.Requery
Else
' Me!subfrmIXODes c!txtInfoXchgOb jNr.Undo
' Me!subfrmIXODes c!txtInfoXchgOb jNr.Value = 0
' etc.
End If

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

Thanks, Ed.

[JConsulting]

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

If Me!.xtInfoXchgO bjNr > 0 Then
Me.subfrmIXODes c.form.xtInfoXc hgObjNr.Default Value = Me.txtInfoXchgO bjNr
Me.subfrmIXODes c.formRequery
Else
' Me.subfrmIXODes c.form.xtInfoXc hgObjNr.Undo
' Me.subfrmIXODes c!txtInfoXchgOb jNr.Value = 0
' etc.
End If

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

[bitsnbytes64]

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

If Me!.txtIXONr > 0 Then
     Me.subfrmIXODesc.form.txtIXONr.DefaultValue = Me.txtIXONr
     Me.subfrmIXODesc.formRequery
Else
'    Me.subfrmIXODesc.form.txtIXONr.Undo
'    Me.subfrmIXODesc!txtIXONr.Value = 0
'    ????
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...colum n 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 :-)

[JConsulting]

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...colum n 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

[bitsnbytes64]

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.

[JConsulting]

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.

[bitsnbytes64]

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.

[JConsulting]

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

[bitsnbytes64]

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).

If Me.txtIXONr > 0 Then
    Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = Me.txtIXONr
    Me.subfrmIXODesc.Form.Requery
Else
   Me.subfrmIXODesc.Form.txtIXONr.Value = 0  'I tried other methods as well
End If
 

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.

[JConsulting]

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).

If Me.txtIXONr > 0 Then
    Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = Me.txtIXONr
    Me.subfrmIXODesc.Form.Requery
Else
   Me.subfrmIXODesc.Form.txtIXONr.Value = 0  'I tried other methods as well
End If
 

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?