Opening a particular form based on ComboBox (1 Viewer)

[Craig6]

Hello,
I think I'm nearly there on a piece of code to open a form. The code below which I harvested, does allow me to open the relevant form based on the support type i choose, however I am trying then to populate the linked field [SupportID] from the first form. The problem is the form could be any one of 5 so the only name i have in the code is strForm and i can't get it to work.
Any ideas? Thanks

Private Sub CmdCreateForm_Click()

Dim strForm As String

If Me.SupportType = "3" Then
strForm = "frmProjects"
ElseIf Me.SupportType = "1" Then
strForm = "frmRemedials"
ElseIf Me.SupportType = "5" Then
strForm = "frmReclamations"
ElseIf Me.SupportType = "2" Then
strForm = "frmUValue"
ElseIf Me.SupportType = "4" Then
strForm = "frmSiteVisits"
Else
strForm = "frmCustomers"
End If

DoCmd.OpenForm strForm, acNormal, "", "SupportID= " & Me.SupportID, acFormAdd
strForm![SupportID] = Me.SupportID
End Sub

 

[pbaldy]

I would have the form name in a hidden column of the combo, but you don't need to specify the form name in the wherecondition. In other words this:

strForm![SupportID] = Me.SupportID

can just be

[SupportID] = Me.SupportID

though you'd have to concatenate:

"[SupportID] = " & Me.SupportID

Also, it doesn't make sense to open the form for data entry AND provide a wherecondition.

 

[Craig6]

So I've created a new column called form name and put the relevant form name to each type and hidden the column.
That means i don't need all of those IF statements - is that right?

So how do i get the button to open the form based on the 3rd column of the combobox?

Thanks

 

[pbaldy]

DoCmd.OpenForm Me.SupportType.Column(2)

The colulm property is zero based, so the third column is 2.

 

[Craig6]

Haha that's great and much easier. Amazing!

Now, the final piece is I'd love to be able to update the SupportID field in the new form that's opened up with the SupportID that I launched the code from. So that the two forms are linked by that ID. Is that possible?

Maybe if not from the same code then onload?
Thanks

 

[pbaldy]

The code you're trying to incorporate is meant to open the form to a record that already exists. If they're going to add a single new record:

DoCmd.OpenForm Me.SupportType.Column(2)

adding the argument for data entry in the appropriate position, then

Forms(Me.SupportType.Column(2)).TextboxName = Me.TextboxName

 

[Craig6]

Wonderful!! I really appreciate it.
With this I can now open a form based on the combobox selection and create a new form with the auto populated SupportID in there.

I just now need to build in an error message for when the form already exists with that SupportID or else i'll get multiple forms with the same number.

You're a star...thanks!!!

 

[pbaldy]

Happy to help! Post back if you get stuck on the duplication prevention.

 

[Craig6]

I'm googling it now! and will probably be back haha

 

[Gasman]

Check for the ID before opening the form using DCount/DLookup ?

Wonderful!! I really appreciate it.
With this I can now open a form based on the combobox selection and create a new form with the auto populated SupportID in there.

I just now need to build in an error message for when the form already exists with that SupportID or else i'll get multiple forms with the same number.

You're a star...thanks!!!

 

[Craig6]

I've found a piece of code for DLookup so i'll have a tweak of it and see if it works. Thanks

 

[Craig6]

Right, I found this piece of code for the DLookup and inserted it before my original code to check if the record exists;

If DLookup("Me.SupportType.Column(3).SupportID", "tblRemedials", "SupportID = " & SupportID) > 0 Then
MsgBox "This card already exists"
Else

DoCmd.OpenForm Me.SupportType.Column(3), acNormal, "", "", acAdd, acNormal
Forms(Me.SupportType.Column(3)).SupportID = Me.SupportID

End If
End Sub

I can't get it to work though, any ideas where I'm going wrong?

 

[Gasman]

If you are using > 0 then use DCount

DLookup returns a value from a field you specify from a table/query with the criteria you use, or NULL if not found.?

You can't use the first parameter like that, that is the field you are looking for.