Need to open form from subform as a new record

[maxxev]

I have found various answers for this in my searching, but unfortunately my knowledge of VBA is extremely basic and I have been unable to decipher the code

The description of what I have (names have been changed for simplicity):

I have a form (form1) this form has a subform (subform1)
(ingredients form and component ingredients subform).

I want the subform1 to open a new form (form2) and automatically giving the user the correct linked ID to enter a new field (countries of origin).

The unique field between them is (SubComp_in_ingID)

Thank you.

 

[TheAceMan1]

How are ya maxxev . . .

Try this:

[blue]   DoCmd.OpenForm "[purple][B][I]FormName[/I][/B][/purple]", acDesign, , , [b]acFormAdd[/b][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Remou]

You will need to select a suitable event for the subform, DblClick for example. Code similar to this is suitable for an event in the subform:

DoCmd.OpenForm "Form2",,,"SubComp_in_ingID=" & Me![SubComp_in_ingID]

Assuming that SubComp_in_ingID is numeric.

 

[maxxev]

Hi, neither option works i'm afraid. (thank you for trying)

Aceman, yours opens the form in deisgn mode

Remou, yours was almost exactly the same as was previously there: here is what the button code looks like:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSubComp_COO"

DoCmd.OpenForm "frmSubComp_COO", , , "SubComp_in_ingID=" & Me![SubComp_in_ingID]

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

The form opens up with a 0 in the ID box (so not linking to the ID on the subform).

Cheers

 

[Remou]

Where is the command button? on the subform or the main form?

Also, add a message box like so:

<...>
MsgBox "SubComp_in_ingID=" & Me![SubComp_in_ingID]

DoCmd.OpenForm "frmSubComp_COO", , , "SubComp_in_ingID=" & Me![SubComp_in_ingID]

What is returned?

 

[maxxev]

Button is on the subform.

 

[maxxev]

The message box does bring up the ID, but is there a way to get this into the new form (frmSubComp_COO)

Cheers

 

[Remou]

I see I too have misread your post:

DoCmd.OpenForm "frmSubComp_COO",,,,acFormAdd
Forms!frmSubComp_COO!txtSubComp_in_ingID = Me![SubComp_in_ingID]

 

[maxxev]

Excellent thank you very much.

 

[maxxev]

lol.
Spoke too soon.

This does work. however because it is in add mode you cannot see an existing entrys.

How do you get the button to open the form in edit mode if there are already entries and add mode if there are none please.

 

[Remou]

Combining the two ideas may suit:

If IsNull(DlookUp("SubComp_in_ingID","Name of table Here","SubComp_in_ingID=" & Me![SubComp_in_ingID]) Then
   DoCmd.OpenForm "frmSubComp_COO",,,,acFormAdd
   Forms!frmSubComp_COO!txtSubComp_in_ingID = Me![SubComp_in_ingID]
Else
   DoCmd.OpenForm "frmSubComp_COO", , , "SubComp_in_ingID=" & Me![SubComp_in_ingID]
End If

 

[maxxev]

i'm sure it's me, but i'm getting a compile error:


Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSubComp_COO"

If IsNull(DlookUp("SubComp_in_ingID","frmSubComponents_in_Components Subform","SubComp_in_ingID=" & Me![SubComp_in_ingID]) Then
DoCmd.OpenForm "frmSubComp_COO", , , , acFormAdd
Forms!frmSubComp_COO!txtSubComp_in_ingID = Me![SubComp_in_ingID]
Else
DoCmd.OpenForm "frmSubComp_COO", , , "SubComp_in_ingID=" & Me![SubComp_in_ingID]
End If

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

 

[Remou]

I am short a parenthesis:

[tt]If IsNull(DlookUp("SubComp_in_ingID","frmSubComponents_in_Components Subform","SubComp_in_ingID=" & Me![SubComp_in_ingID])) Then[/tt]


For future reference, please use [ignore]

[/ignore] for code and please mention which line is giving the compile error.

 

[maxxev]

The microsoft jet database engine cannot find the table or query"
Is the message I get after adding the last bracket. (It is definately spelt right).

Assuming I should be using the subform and not the form name?

 

[maxxev]

http://i76.photobucket.com/albums/j13/maxxev/Error1.jpg

 

[Remou]

So is frmSubComponents_in_Components Subform "Name of Table Here"? It seems unlikely.

 

[maxxev]

After sepdning 20 or so mins looking at this, I just dawned that you said table not form.... whoops.

It is now working correctly.

Thank you again

Hopefully I wont be back (at least not on this question...)

 

[maxxev]

Hi I used this same code above to do the same thing on another sheet, and it was working.

At some point however it has crashed and is now no longer working... any thoughs please?

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSample_Request_Table"
    
   If IsNull(DLookup("tblNPD_Project_NPDProjectID", "tblNPD_Project", "tblNPD_Project_NPDProjectID=" & Me![tblNPD_Project_NPDProjectID])) Then
    DoCmd.OpenForm "frmSample_Request_Table", , , , acFormAdd
    Forms!frmSample_Request_Table!txtNPDProjectID = Me![tblNPD_Project_NPDProjectID]
Else
   DoCmd.OpenForm "frmSample_Request_Table", , , "NPDProjectID=" & Me![tblNPD_Project_NPDProjectID]
End If
Exit_Command24_Click:
    Exit Sub

Err_Command24_Click:
    MsgBox Err.Description
    Resume Exit_Command24_Click
    
End Sub

 

[maxxev]

UPDATE.

The problem actually seems to be with the frmSamples_Request_Table, even when opened outside of any links (straight from the database) it is blank even though it has one record, I have no idea what to do, it was working fine before the crash and I have tried a correct and repair.

I do have a DB backup but it is before I had it working

 

[maxxev]

Issue solved by re-creating form, sorry for bombarding the board!