Think I am getting it in the wrong order

[fat controller]

I have code that works when parts of it are executed separately, but put together it either partly works or does not work at all. I have tried a couple of variations with little luck.

Variation 1

If Me.tckRtn10 = True Then

If Me.txtRtnRef.Value = Null Then
Dim strFrmName As String

strFrmName = "Form2"
DoCmd.OpenForm strFrmName, acNormal
With Forms(strFrmName)
.txtEmployee_Number = Me.txtEmployee_Number
.txtInitials = Me.txtInitials
End With

Else
Dim varInput
varInput = [Forms]![Form1]![txtRtnRef]

DoCmd.OpenForm "Form2", acNormal
Me.Our_Order_Number.SetFocus
DoCmd.FindRecord varInput, acEntire, , acSearchAll, False, acCurrent

End If
End If
End Sub

Variation 2

If Me.txtRtnRef.Value = Null Then
If Me.tckRtn1 = True Then
Dim strFrmName As String

strFrmName = "Form2"
DoCmd.OpenForm strFrmName, acNormal
With Forms(strFrmName)
.txtEmployee_Number = Me.txtEmployee_Number
.txtInitials = Me.txtInitials
End With
End If
End If

If Not IsNull(txtRtnRef) Then
If Me.tckRtn1.Value = True Then
Dim varInput

varInput = [Forms]![Form1]![txtRtnRef]

DoCmd.OpenForm "Form2", acNormal
Me.Our_Order_Number.SetFocus

DoCmd.FindRecord varInput, acEntire, , acSearchAll, False, acCurrent

End If
End If

Essentially, what I am trying to achieve is:

• When checkbox is ticked, and there is nothing in the textbox txtRtnRef, I want Form2 to open and the values from txtEmployee_Number and txtInitials passed over (this works on its own)
• When checkbox is ticked, and there is a value in the textbox txtRtnRef, I want Form2 to open, the control Our_Order_Number to get focus, and then find record(s) that match the text contained in txtRtnRef on Form1 (this also works on its own)
• If the checkbox is unticked, I don't want anything to happen.

Grateful for any and all advice

 

[pbaldy]

Think of Null as unknown and you'll understand that you can't use = to test for it. Try the IsNull() function or the test here:

http://www.baldyweb.com/BeforeUpdate.htm

 

[fat controller]

I have tried to simplify the process by adding a button to the form instead of having an OnUpdate event on each tick box.

Broken down, the code works, put together it doesn't. The code breaks down as follows:

If IsNull(txtRtnRef) Then
Dim strFrmName As String

strFrmName = "Form2"
DoCmd.OpenForm strFrmName, acNormal
With Forms(strFrmName)
.txtEmployee_Number = Me.txtEmployee_Number
.txtInitials = Me.txtInitials
End With

End Sub

That will open Form2 and pass on the relevant details

Dim varInput
varInput = [Forms]![Form1]![txtRtnRef]

DoCmd.OpenForm "Form2", acNormal
DoCmd.GoToControl "ReOrder_Number"

DoCmd.FindRecord varInput, acEntire, , acSearchAll, False, acCurrent


End Sub

And that will open the same form and find a record with the text matching the value of txtRtnRef on Form1

However, I want the two to work together:

If txtRtnRef is Null, then run the first bit of code; if it has a value, then run the second bit of code to find the corresponding record.

I tried making doing it with

 If txtRtnRef = Null Then {first bit of code} 
Else
{Second bit of code}
End If

But it doesn't work?:banghead:

 

[fat controller]

Think of Null as unknown and you'll understand that you can't use = to test for it. Try the IsNull() function or the test here:

http://www.baldyweb.com/BeforeUpdate.htm

Sorry, I must have been typing around the time you were.

The closest I have managed is

If IsNull(txtRtnRef) Then
Dim strFrmName As String

strFrmName = "Form2"


DoCmd.OpenForm strFrmName, acNormal
With Forms(strFrmName)
.txtEmployee_Number = Me.txtEmployee_Number
.txtInitials = Me.txtInitials
End With

Else
Dim varInput
varInput = [Forms]![Form1]![txtRtnRef]
DoCmd.OpenForm "Form2", acNormal
DoCmd.GoToControl "ReOrder_Number"
DoCmd.FindRecord varInput, acEntire, , acSearchAll, False, acCurrent
End If
End Sub

Now, if there is text in txtRtnRef, clicking this button will find the related record - so far so good; BUT, if there is nothing in txtRtnRef, it opens the form to the last record and does not pass any data - - it seems to be completely ignoring that part of the code?

 

[pbaldy]

Have you set a breakpoint and stepped through the code? You might add a DoEvents after opening the form to make sure it is open in time to set the values.

 

[fat controller]

Have you set a breakpoint and stepped through the code? You might add a DoEvents after opening the form to make sure it is open in time to set the values.

Wouldn't have a clue how to do that

I am thinking of separating the two out and setting an After Update event on the Form - of txtRtnRef is Null - show the button to create a new record and push the data over, if not show the 'find' record - whether that will work or not I have no idea.

 

[pbaldy]

Here's how to do that:

http://www.baldyweb.com/Debugging.htm

 

[fat controller]

I more or less scrapped the whole lot of this part of the project and started from scratch with it last night - - the further I went into it, the more it became apparent that I hadn't really designed either of the two tables particularly well, and that was leading to it being inefficient and harder to work with than it should be.

A wee while with Excel and a far better, single table, getting shot of duplicate data and it now works really quite well - - I am now onto finishing off some of the 'niceties' on the forms.

I've bookmarked your link for future too