Subform ODBC Error When New Record On Parent Form - Page 2

31 New Member

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.

May 4 '07

Subscribe Reply

3277

bitsnbytes64

New Member

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

603

Recognized Expert Contributor

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

 
If Me.txtIXONr > 0 Then

    Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = Me.txtIXONr

    Me.subfrmIXODesc.Form.Requery

Else

   Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = ""

   Me.subfrmIXODesc.Form.Requery   

End If

May 8 '07 #12

bitsnbytes64

New Member

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

If Me.txtIXONr > 0 Then

    Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = Me.txtIXONr

    Me.subfrmIXODesc.Form.Requery

Else

   Me.subfrmIXODesc.Form.txtIXONr.DefaultValue = ""

   Me.subfrmIXODesc.Form.Requery

End If

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

bitsnbytes64

New Member

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.txtHierObjRe lNr > 0 Then

' refresh subform subfrmIXF

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

' refresh subform subfrmIXFDesc

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

' refresh subform subfrmIXFNotes

Forms![frmIXF]![subfrmIXFNotes]![txtIXFNr].DefaultValue = Me.txtHierObjRe lNr
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

Similar topics

4266

Error when Adding Record to Linked Subform

by: Andante.in.Blue | last post by:

Hi everyone! In Access 97, I have a parent form and a subform that are both based on the same query. The main form is continuous and serves up a summary for each record. The subform provides details on the record and is linked to the parent record via an autonumber ID. My users want to be able to add new records to the table underlying the main query (the query is structured so that new records can be added via its database view). I...

Microsoft Access / VBA

2036

The old subform trick.....

by: Ray | last post by:

I have a main form with a subform. The main form has a combo box which lists all the clients which in turn are displayed in the subform. The subform is bound to the combo box and all work well. however, I would like to be able to add a new client in the subform but this is, of course bound to the combo box. Can anyone tell me if there is a way to enter a new client in the subform with out receiving an error message? TIA - Ray

Microsoft Access / VBA

10264

Recordset in subform based on field in parent form

by: Lyn | last post by:

Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list...

Microsoft Access / VBA

10828

Unbound Nested Subform Simulating Continuous Form

by: Robert | last post by:

Need some help to stop me going around in circles on this one.... Have a nested subform (subform2) which simulates a continuous form for the record on the parent subform. Subform2 has rows of either an option button plus two text fields or a checkbox plus two text fields Am wanting to save the user entries into an underlying table. Tag property for each option button, check box or text field has the value of the key

Microsoft Access / VBA

4902

Using subform to go to record in subform

by: Robert | last post by:

I have a form with two subforms on it. both subs have the same controlsource. Subform1 is a continuous form that acts as an index. The second subform is a single record that the user will use to edit the record. There is a button (transparent) over the record on subform1 that when clicked should force the second subform to goto that record. Any thoughts on how to go about this? Thanks Robert

Microsoft Access / VBA

3236

Subform data not saving when focus moves to parent

by: google | last post by:

I have a form with several subforms. Users enter the data, then on the parent there is a command button that runs code to generate a .pdf document from a report based on the data they are working with. If a user enters data in a subform, then directly clicks the command button on the parent form, the data in the subform is not included in their document. I THOUGHT that as soon as focus left the subform, any pending changes to the data...

Microsoft Access / VBA

4025

Main form saving when click on subform??? A97

by: MLH | last post by:

I have a form, frmVehicleEntryForm. On it is a subform control named frmAddnlOwnrListSubForm. The subform control's source object is frmAddnlOwnrListSubForm. When I click on the subform control, an attempt to SAVE the partially entered record on the main form is attempted. I do not understand where this behavior is coming from or why. Haven't a clue where to look. Anyone familiar with this behaviour? Here's my subform control's Enter...

Microsoft Access / VBA

3004

Populating fields when adding new record via datasheet subform

by: pzh20 | last post by:

I have an unbound form/subform where I populate a combo box on the main form, and using the onchange event, display fields from a table in a datasheet subform. I want to add a new record via the subform, allowing the user to type certain fields but complete other fields via code in the beforeinsert event as follows Private Sub Form_BeforeInsert(Cancel As Integer) Me.Kitchen = Me.Parent.Kitchen(2) Me.lookupRoute = Me.Parent.Kitchen(1) ...

Microsoft Access / VBA

3601

Form/ subform problems

by: troy_lee | last post by:

I have a table (Table A). It has one field, a PK. It is in a 1:M with another table (Table B). I am having trouble with a form/subform setup to view the 1:M records. On the parent form, there is a combo box with a Select query to select one of the records in Table A. (The parent form is bound to Table A.) I want to be able to first, view all of the corresponding records from Table B and then second, give an option for editing these...

Microsoft Access / VBA

9568

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9398

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10156

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10007

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9951

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

6649

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5275

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5419

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3531

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP