Open form where

[speakers_86]

Private Sub AddNewTransaction_Click()
DoCmd.OpenForm "frmEquipmentTransfers", acFormDS, , [Forms]![frmEquipmentTransfers].[Combo47].Column(4) Or [Forms]![frmEquipmentTransfers].[Combo47].Column(4) = Me.WorkOrderID
End Sub

I think I can see why this doesnt work. I think it is because I am referencing the form before it is open. Can someone show me how I should go about doing this?

 

[jsv2002]

I have used Dcount in the past to check for an existing record then if not used openargs to create a new one like:

On Error GoTo Err_OpenRisk_Click

Dim stDocName As String
stDocName = "frmPPERisks1"

'Checks the table for duplicate entries prior to passing OpenArgs array if true goes to In List Sub.

If DCount("*", "tblPPERisks1", "[FormID] = " & Me.FormID & "And[PPEID]=" & Me.PPEID) = 0 Then
Else
GoTo In_List_Click

End If

'Opens the specified form and puts selected records into an array for use in the SplitMod Module which
'is activated in the form On Load code which seperates the array into seperate string values for use
' as OpenArgs which are the allocated to the form fields

DoCmd.OpenForm stDocName, , , , acAdd, acDialog, Forms![Form1].[FormID] & ";" & Me.PPEDescription & _
";" & Me.PPEType & ";" & Forms![Form1].[SiteID] & ";" & Me.PPEID & ";" & Forms![Form1].[DepartmentID] _
& ";" & Forms![Form1].[EmployeeID]

Exit Sub

In_List_Click:

If MsgBox("The PPE Item already has a risk assessment do you wish to view it?", vbYesNo) = vbNo Then

GoTo Exit_OpenRisk_Click

Else
stLinkCriteria = "[FormID]=" & "" & Me![FormID] & "" & " AND [PPEID]=" & "" & Me![PPEID] & ""

DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Exit Sub

Exit_OpenRisk_Click:
Exit Sub

Err_OpenRisk_Click:
MsgBox err.Description
Resume Exit_OpenRisk_Click

End Sub

Don't know if this will help but seems your trying to do a similar thing?

regards John

 

[speakers_86]

It seems the code I need should be simpler than that.

All I am doing, is opening a form in datasheet, where column 4 of field combo47 OR combo49 equals the workorder id.

Although, because of the PPE field in your code, I am curious as to what kind of db your code came from.

 

[jsv2002]

Its a coshh assessment database I've been working on for a while... this particular bit checks if you have already done an assessment for the PPE (Personal protective equipment) then based on code will open exising or create new record.

Sorry didn't trim it down but just thought the general idea of Dcount might be useful to select your record

regards John

 

[speakers_86]

Thanks John. Will you be posting it here? Id love to take a look, as my db is designed for water damage/mold remediation.

Any other suggestions?

 

[DevastatioN]

If you are just trying to filter the form... you can use:

strFilter = me.WorkOrderID
DoCmd.OpenForm "frmEquipmentTransfers", acFormDS,
Screen.ActiveForm.Filter = strFilter
Screen.ActiveForm.FilterOn = True

I don't believe you can use the link criteria to open a form for a certain column of a combobox, unless it's a bound combobox and the value is what is bounded.

 

[speakers_86]

I believe you are right, and that you may be closer to the answer than me. Ill try it when I get off work.

 

[speakers_86]

Here's what Ill do. Ill add 2 more fields to the table. In the form, on the update events Ill set the value of the 2 new fields to equal column 4. Then I should be able to filter it like that.

 

[Simon_MT]

I would create a control in frmEquipmentTransfers for:

47.4 = [Combo47].Column(4)
49.4 = [Combo49].Column(4)

Public Function FEXfer()
 
    With CodeContextObject
        DoCmd.OpenForm "frmEquipmentTransfers", acFormDS, , "[47.4] = " & .[WorkOrderID] & "' Or [49.4] = " & .[WorkOrderID]
     End With
End Function

I don't use subs so you might want to change things around.

Simon

 

[speakers_86]

Still having a hard time.

Private Sub AddNewTransaction_Click()
DoCmd.OpenForm "frmEquipmentTransfers", acFormDS, , "[ToWorkOrderID] ="  & Me.[WorkOrderID] Or "[fromworkorderid]=" & me.[workorderid]
End Sub

I think the problem is that I am filtering it by 2 criteria. If I only use one criteria, it works fine. Ideas?

 

[Simon_MT]

Are they the same values?

Simon

 

[boblarson]

No, your problem is that your OR is not inside the quotes where it needs to be:

Private Sub AddNewTransaction_Click()
DoCmd.OpenForm "frmEquipmentTransfers", acFormDS, , "[ToWorkOrderID] ="  & Me.[WorkOrderID] & " Or [fromworkorderid]=" & me.[workorderid]

 

[WayneRyan]

Speakers,

You're "OR"ing two strings:

DoCmd.OpenForm "frmEquipmentTransfers", acFormDS, , "[ToWorkOrderID] =" & Me.[WorkOrderID] Or "[fromworkorderid]=" & me.[workorderid]

Change to:

DoCmd.OpenForm "frmEquipmentTransfers", acFormDS, , "[ToWorkOrderID] = " & Me.[WorkOrderID] & " Or [fromworkorderid] = " & me.[workorderid]

Wayne

 

[speakers_86]

That gives me a data type mismatch. All 3 fields are number fields. The form is opened in datasheet, should this work for a datasheet form?

 

[speakers_86]

Its because of nulls. Im working on a fix now.

 

[speakers_86]

Can anyone tell me why this works

Private Sub Combo49_AfterUpdate()
Me.ToWorkOrderID.Value = Me.Combo49.Column(3)
If IsNull([ToWorkOrderID]) Then
Me.ToWorkOrderID.Value = 0
End If
End Sub

but this does not

Me.FromWorkOrderID.Value = Me.Combo47.Column(3)
If IsNull([FromWorkOrderID]) Then
Me.FromWorkOrderID.Value = 0
End If

The if statement does nothing, but the first line works.

 

[boblarson]

Are you sure that the Combo47 has at least 4 columns and has the "number of columns" property set to 4 (or above)?

 

[speakers_86]

The column count is set to 4. Curiously, if I change the code to this

Me.FromWorkOrderID.Value = Me.Combo47.Column(4)
If IsNull([FromWorkOrderID]) Then
Me.FromWorkOrderID.Value = 0
End If

the first part does not work, and the if statement does. This is expected, but I dont get why it doesnt work set to column 3. Ive also tried columns 1 and 2, but they dont contain the workorder id. The code works with columns 1 and 2.

 

[speakers_86]

I feel like a fool. After staring at the code for 30 minutes, then recreating the form and getting the same result, I took a second look at the table. FromWorkOrderID was still set to a text field.

Problem is solved. Thanks for everyones help.

This works.

DoCmd.OpenForm "frmEquipmentTransfers", acFormDS, , "[ToWorkOrderID] ="  & Me.[WorkOrderID] & " Or [fromworkorderid]=" & me.[workorderid]