Adding new records to forms

[pablavo]

Hi folks

I have a frmMain that is used to add details about projects. I have another form (subform if you will) frmContacts (the backend tables are linked to each other) and this will be related contact details. I don't have anymore room on the main form and I can't use a Tab control.

So I was thinking of using a command button to open the Contact Form to add the details. I've tried to use the Command button wizard to link the forms so that I can click to open the contact form I can enter the details. However, The dialog tells me that there has to be a related record in the Main table etc. So I need to do more than this.

does any one know how this would work? I'm not really sure if I need to add code so that I can use the subform like this. Or maybe there's another way?

Thanks for any help that I get on this one

 

[fugifox]

The wizard simply tells you that you can open the subform (that is Contacts in your case)
either by listing all available records in Contacts table or only those relating to your master form that is frmMain.
From your description I assume you need the first way, so follow the wizard steps and link
the two forms using the same fileds you use to link the relative tables

 

[pablavo]

I've already tried using the wizard and the code for that is:

Private Sub cmdOpen2_Click()
On Error GoTo Err_cmdOpen2_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContacts"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpen2_Click:
Exit Sub

Err_cmdOpen2_Click:
MsgBox Err.Description
Resume Exit_cmdOpen2_Click


I've also tried adding code and go this:

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContacts"

stLinkCriteria = "[ProjectID]=" & Me![ProjectID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Click

End Sub

And none of these work.

The next code technique I tried was to toggle the subform close and open which was:

Private Sub cmdTogglefrmContacts_Click()
Me![frmContacts].Visible = Not Me![frmContacts].Visible
End Sub

And this is messy because in design view the subform is put in top of controls and stuff on the other form. I would rather that I could open the subform and when it's closed, it's closed.

As I said, the wizard code doesn't work. anyone got any ideas?

Thanks

 

[ajetrumpet]

The dialog tells me that there has to be a related record in the Main table etc.

That is an effect. The cause of this is trying to create a record in a child table without a relation in its parent table.

I'm not really sure if I need to add code so that I can use the subform like this.

Using the subform as a popup mechanism?? No, you don't need much code to do this. What you do have to make sure of, is that you have a record entered in the parent table before you try entering one in the child.

 

[pablavo]

thanks for replying folks.

There is already a record created within the main form. With the code above (apart from the form toggle code) used. none of them works. So I'm obviously missing something.

I believe the most effecient way for me is a command button that opens a form that is linked to the main form and I can add a new record to the "subform" and then close it. The code above still comes gives me "you need related record in tblMain.

 

[ajetrumpet]

Here are some helpful facts when dealing with something like this Pablavo (maybe they will prove to be useful)....

** If you create a form based on a main table after relationships have already been set up between the parent and child tables, Access will automatically insert a subform (based on the child table) into the main form's window as a control, but not a separate object.

** Defining Referential Integrity in a relationship is a common cause of this error. Its purpose is to "force" data validation between related tables. If RI is not enforced between ANY related tables, the master/child relation is almost completely useless, but not totally.

One idea of what might be causing the error message that you see...

Trying to enter child table data (contact info.) into the child form (or subform) when the relationship between the tables does not complement. In other words, in your case, trying to enter contact information into the contact form, even though there IS a project already entered in the main form (but the catch is...what field is joined in the relationship??? If it is the "contact" field instead of the "project" field, than the message is correct). Check to see what field is joined in the relationship...

 

[pablavo]

First of all, Id like to thankyou Ajetrumpet for taking the time to help me with this one.

I'd like to state that I'm aware of how relationships work and this is not a relationship/field problem, this is an MS-access wizard problem; to be more precise, the wizard doesn't help me on doing what I want done. The wizard will only create code that will let me link two forms that already have existing ProjectIDs. If there is no record set up within tblContact, then when I open frmContact via a command button in frmMain I get the form but the ProjectID (FK) is "0".

The fields within the tables tblMain and tblContact are joined via the ProjectID as you can tell from the code above! the ProjectID is the PK (autonumber) for tblMain and FK for tblContact.

I've looked at some other code that I can give a try. If it works i'll post it here incase anyone else is having the same problems.

Cheers

 

[ajetrumpet]

Well, I hope the new code will work. I guess I'll say that I am not really of fan of wizards, as it's difficult to interpret (exactly) what they do for you!!

Given the fact that the wizards are like "pre-set" instruction bases, I don't think they respond well to "customization". Here is another method that might be a good substitute for you if all else fails...

** If you want the subform to pop up with the autonumber value from the main form in the "projectno" field, you can simply write a DoCmd.Openform command for a button, along with an assignment for the value of the "projectno" field. You could assign the value of the FK field when the form loads. Something like...

OnLoad

  Me.FKfield = Forms!MainForm!PKfield

And, the PK showing up as "0" does make sense, because if there are no records in the child table that relate to a parent table record, there is no way that Access can differentiate between its programming and you're intentions to assign a new record. It doesn't "guess", it "reads" current data and what IS at the
time of the proposed request.

 

[pablavo]

Thanks AjeTrumpet!
that was the code I was going to try out! plus, in the record source of the Contact form I was going to add the ProjectID from tblContact and add
=forms!frmMain!ProjectID

I wouldn't think this was essential, however, it does no harm.

Cheers