Problem linking & auto updating from main form to subform 1

[staciann]

Hi. I have two tables: tbl_Solicitations and tbl_Comments. They are linked in a one-to-many relationship by the fied ID (autonumber;integer). The purpose of tbl_Comments is to hold multiple updates added about each solicitation listed in tbl_Solicitations.

I then have a main form, frm_Solicitations (based off of tbl_Solicitations) with 2 subforms in it: frm_SubViewComments and frm_SubAddComments (both based off of tbl_Comments).

frm_SubViewComments is a form in table view that simply shows comments that have been added about the solicitation and frm_SubAddComments is a text form with a submit button where the user can add a new comment.

What I want to have happen is a user can go in and create a record for a new solicitation in tbl_Solicitations. Then can input a comment/update in frm_SubAddComments, hit submit and it will automatically show up in the table above it, frm_SubAddComments. Then periodically, a user can go in and add new updates/comments to the record and still be able to see all previous comments/updates in frm_SubViewComments above.

When I create a new record in frm_Solicitations, a new ID (autonumber) is created after I update the first thing on the form. However, the ID is not being updated in frm_SubAddComments to match; it just continues to say "(New)". This is causing a problem for 4 reasons:

1) When I type in a comment and hit 'Submit', the comment is being added to tbl_Comments, but it is not updating on frm_SubViewComments and isn't showing up in the table.

2) The ID field in tbl_Comments is not being updated to match the ID field in tbl_Solicitations.

3) All comments for all solicitations are showing up on each solicitation. (ei. Solicitation 9 shows comments from Solicitations 2 and 3)

4) When I try to add another record, I get the error "The Microsoft Access database engine cannot find a record in the table_Solicitations' with key matching field(s) 'ID'.

I'm not sure if this has to do with how I have the relationship set up between the ID fields or if it has to do with needing an update event taking place in tbl_SubAddComments or what.

Any help would be appreciated. I have attached an image of my form so you can get a better idea of what I have going on.

Thanks!

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

You must save the tbl_Solicitations record prior to submitting the comments. Typically this is done in a bound form with code like:

  me.dirty = false
  'other code here that might use the ID field value

The subform control hosting frm_SubViewComments should have its Link Master/Child properties set the the primary key/foreign key field names.

Duane
Hook'D on Access
MS Access MVP

 

[staciann]

I added that code, but it is still not working.
(I don't know much about VBA)

The main form is bound and the properties are set in the subform.

I am now also getting an error that says, "Field can not be updated" as soon as I add a new record. If I keep going, it does actually update the table, but I know this will be a nuisance to users.

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

I don't know where you added the code. I expect it should be at a point where all of the required field values have been entered.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya staciann . . .

I don't know why your using a duplicate subform for data entry (this is typically not done), but might I suggest an [blue]unbound textbox[/blue] for this purpose! To polish it off an update query or SQL updates [blue]tbl_Comments[/blue], while a final requery updates [blue]frm_SubViewComments[/blue]. The SQL and Requery would have to be intergrated in your submitt code, so post this code ... we need to take a look anyway.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

This can be done using bound forms, you just do not have it set up properly.

Main form based on tbl_Solicitation

subFrmList: a subform of comments in continous form view linked to the main form by [fied ID]

subFrmAdd: a subform of comments in single form view, data entry set to yes, linked to the main form by [fied ID]. No record selectors or navigation buttons.

subFrmAdd has a cmd button to commit the addition and requery subFormList so that you can see it in the list and then move to a new record so you can add another comment.

Private Sub cmdAdd_Click()
On Error GoTo errlbl
Me.Dirty = False
Me.Parent.subFrmList.Requery
DoCmd.RunCommand acCmdRecordsGoToNew
Exit Sub
errlbl:
If Not Err.Number = 2046 Then
MsgBox Err.Number & " " & Err.Description
End If
End Sub

 

[TheAceMan1]

MajP . . .

I query! ... Simulate this and tell me if you can call-up the [blue]Data Entry[/blue] property for [blue]frm_SubAddComments[/blue]! I have 2003 and was not able to do so! Let alone most of the other form properties!.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Maybe, I am misunderstanding the OP. But here is my interpretation. There should be no reason why one subform can not be in continous view and another in single form view and data entry. This seems somewhat basic, so my guess we are interpreting this differently.

http://www.4shared.com/document/0LaQo8N-/Solicitation.html

 

[TheAceMan1]

Roger That [blue]MajP[/blue] . . .

It was the way I made the forms.


So what happen to [blue]staciann[/blue]? ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[staciann]

Hi guys - thanks for your help. i've been busy working on other things all day. I added in your code MajP and I am getting the following error code:

"2465 - Application-defined or object-defined error"

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[MajP]

Check that the code uses your actual control names.
My guess
Me.Parent.subFrmList.Requery
your subform control is not called subFrmList