Multiple tasks in vb code

[JFo]

Having some real difficulty with this code which is essentially 3 parts that I am trying to combine



Private Sub cmdAddNewEquip_Click()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim x As Integer

x = MsgBox("Are you sure you want to save changes?", 4, "Exit?")

'VbNo constant throws back #7
'VbYes constant throws back #6

If x = 7 Then
Exit Sub


Else


If Me.[cmbEquipType].Value = "1" Then
DoCmd.OpenForm "frmEquip1"
Else
Me.[cmbEquipType].SetFocus

End If

If Me.[cmbEquipType].Value = "2" Then
DoCmd.OpenForm "frmEquip2"
Else
Me.[cmbEquipType].SetFocus

End If


db.Execute " INSERT INTO [tblEquipmentInspectionJunction]" & "([EquipmentID], [InspectionID]) VALUES " _
& "('" & Me.Controls("txtEquipment").Value & "'," & "'" & Me.Controls("txtInspection").Value & "'" & ");"



DoCmd.Close

End If


End Sub

The first part is asking whether you want to save changes and then should save to table and close form, the second part chooses the form to open dependent on the combo box selection and the 3rd part enters values in another table.

I have tried in all different orders and arrangements to try and combine and cannot get all 3 things working in one sub. they all work fine in separate subs but I want to combine them

Thanks for your help

[June7]

Why do you have two Equipment forms? Two equipment tables? Why? Are they really different structures?

Do you want the form to close if response is no? The SetFocus is irrelevant because you are closing the form. Should the record be saved before opening the other form? Do you want form to open filtered to specific record? Use the WHERE argument of OpenForm method.

If x = 6 Then

If Me.cmbEquipType = "1" Then
DoCmd.OpenForm "frmEquip1"
ElseIf Me.cmbEquipType = "2" Then
DoCmd.OpenForm "frmEquip2"
End If

or use this one-liner
DoCmd.OpenForm IIf(me.cmbEquipType ="1", "frmEquip1","frmEquip2")

db.Execute "INSERT INTO tblEquipmentInspectionJunction(EquipmentID, InspectionID) VALUES " _
& "('" & Me.txtEquipment & "', '" & Me.txtInspection & "');"

End If

DoCmd.Close

[JFo]

Hi June 7, tried playing with your code and still can't nut this one out, in answer to your questions

Why do you have two Equipment forms? Two equipment tables? Why? Are they really different structures?

I have multiple equipment forms as the equipment is completely different, I do not have multiple equipment tables however as 1 inspection inspects many pieces of equipment and 1 piece of equipment can be involved in many inspections I have a junction table to link equipment to inspections

Do you want the form to close if response is no?
No I only want to close the form if you want to save changes/ or open form

The SetFocus is irrelevant because you are closing the form.
I noticed this just error catching if item is not on list but doesn't work

Should the record be saved before opening the other form?
Yes and no, I was originally trying to do this however what I would ideally want to do is leave the form open while the frmEquip1 or frmEquip2 are open and then if cancelled then it remains open, otherwise if saved then both forms should close and be saved to tables (therefore want the second form to save and close both forms if not cancelled)

Do you want form to open filtered to specific record? Use the WHERE argument of OpenForm method.
Yes, I will want the form filtered to default values (for specific piece of equipment) for first time ever inspected if this was a new piece of equipment, however if it was inspected before I would like it to open to the last values that it had input

Thanks

[June7]

Can try this. The acDialog will suspend code execution from the calling form until called form is closed. If you want to provide project I will look at.

If x = 6 Then

db.Execute "INSERT INTO tblEquipmentInspectionJunction(EquipmentID, InspectionID) VALUES " _
& "('" & Me.txtEquipment & "', '" & Me.txtInspection & "');"

DoCmd.OpenForm IIf(me.cmbEquipType ="1", "frmEquip1","frmEquip2"), , , , , acDialog

DoCmd.Close

EndIf

[JFo]

Thanks June 7 I got it working however I do not want to close the form from the command button in first form I want to save it and close it only when saving second form.

What is simple code to close the current form and another form at same time?

something like both these together

DoCmd.Close "frmEquipment"
DoCmd.Close


However I get type mismatch error

Cheers

[June7]

Doesn't the acDialog accomplish that? Not sure I understand requirement. Are you saying user allowed to abort the entire process from second form?

[JFo]

yes user can abort process from second form and therefore first form is not closed/ saved, however if user completes second form then both forms should be saved/ closed

Notably there is more than 2 forms.


1 other problem I have is that the process works with no relationships between equipmentID to junction table, and
inspectionID to junction table

however as soon as I add the relationships nothing goes into the table (however I do not receive any errors messages), any ideas

Thanks

[June7]

One possible solution. Create a textbox on main form, it can be set not visible, call it tbxDone

Code on main form:
If x = 6 Then
DoCmd.OpenForm IIf(me.cmbEquipType ="1", "frmEquip1","frmEquip2"), , , , , acDialog
'change back to the If Then ElseIf structure if you need to.

If Me.tbxDone = "Save" Then
db.Execute "INSERT INTO tblEquipmentInspectionJunction(EquipmentID, InspectionID) VALUES " _
& "('" & Me.txtEquipment & "', '" & Me.txtInspection & "');"
DoCmd.Close
End If

End If

Then from second form(s) set value of tbxDone if want to proceed with save.
Form!mainformname.tbxDone = "Save"

[JFo]

Took my ages to figure it out but thanks to June 7 it works

You have helped me with my project a great deal and I am very appreciative