Incorrect record???

[WinDancer]

The code below will not open the correct record- that is, the record that matches the tracking number.

It runs agains the very tracking number instead.

If I put this code on a button and run it after the record loads it works correctly.

Help with the code, and a little VBA lesson, please?

DoCmd.OpenForm "frmClaimAllowanceDrillDown", acNormal, , "[TrackingNumber]=" & Forms!frmlocalrecords!TrackingNumber
Pause (3)
If Forms!frmclaimallowancedrilldown!Changed = -1 Then
cmdClaimAllowance.ForeColor = vbRed
Else
cmdClaimAllowance.ForeColor = vbBlack
End If
DoCmd.Close acForm, "frmClaimAllowanceDrillDown", acSaveNo

Thanks,
Dave

 

[pbaldy]

I'm not clear what the problem is. You say it works correctly from a button. Where is it not working? What are you trying to accomplish? From the look of it, you should just use DLookup or a recordset to get the value.

 

[WinDancer]

I just need the record [trackingnumber] on the second form to open to the matching record on the first form.

Right now it just displays the first record [lowest tracking number] of the second form.

 

[pbaldy]

Well, I use that technique all the time, so I know it works:

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

I'd make sure the control being referenced contains what you expect. That said, you're opening the form, checking a value, then closing it again. My point is that it would be much more efficient to use DLookup or open a recordset to get that value rather than opening a form.

 

[WinDancer]

The problem I am trying to solve is getting the second form to go to the matching record when it opens. After it is open for a few seconds it then loads the correct record.
There is another process that runs that depends on the two records matching. The pause between opening and matching is the problem.

My goal is to turn the font on a button on the first form RED if there a condition on the second form is met, and Black if that condition is not met.

If there is an easier or more efficient way to do that I would be happy to try a different way of accomplishing the task.

I believe a form must be open to get a value from it?

 

[pbaldy]

The form must be open to get a value from the form, but not to get a value from the underlying data. A DLookup with the same criteria may work:

http://www.mvps.org/access/general/gen0018.htm

 

[WinDancer]

so trying to use your sample code I now have:

If DLookup("Changed", "TblClaimAllowance", "TrackingNumber = forms!localrecords!TrackingNumber") =-1 Then
MsgBox "code is OK"
Else: MsgBox "Code not running"

It returns code not running because I don't have all the pieces in place yet-
I need to match the value for trackingNumber and then see if the field 'Changed' in the table contains the value "-1"
How do I get both the matching trackingnumber and the "changed" value into the statement?

Thanks,
Dave

 

[pbaldy]

That doesn't quite follow the link:

If DLookup("Changed", "TblClaimAllowance", "TrackingNumber = " & forms!localrecords!TrackingNumber) =-1 Then

I'm not clear on what you're asking, because the above should do this: I need to match the value for trackingNumber and then see if the field 'Changed' in the table contains the value "-1"

 

[WinDancer]

Exactly what I am trying to do.

If DLookup("Changed", "TblClaimAllowance", "TrackingNumber = " & Forms!frmlocalrecords!TrackingNumber) = -1 Then
cmdClaimAllowance.ForeColor = vbRed
Else
cmdClaimAllowance.ForeColor = vbBlack
End If

does not change the button font back to black.

If the button was RED for the last record it stays red for the new one too, evein if changed for the new record = "0" [the changed field is Yes/No].

 

[pbaldy]

Can you post a sample db?

 

[WinDancer]

The db won't run unless coneected to our data warehouse and contains confidential info.
Thanks for trying
Dave

 

[pbaldy]

Understandable. Have you set a breakpoint and examined the value coming from Forms!frmlocalrecords!TrackingNumber to make sure it's what you expect?

 

[WinDancer]

Sure have- my little brown buddies that check my code without telling anyone

I have been working on this since last Thursday and am pretty frustrated with myself.

The tracking number that the code asks for is correct- when i look at the first line of code the TrackingNumber matches for both forms.

When I step through manually to color the font on the buttons the red and black work correctly.


Once the button font changes to red it is that way for all records you open after that.

When I put the same code on another button to run from the main form, it changes to match the correct color, red or black as appropriate.

But when it runs in the on open event of the main form it will not reset the color to black.

 

[pbaldy]

Have you tried that code in the current event of the form to catch the changing of records?

 

[WinDancer]

No i haven't- just add the same code to on current?

 

[pbaldy]

Worth a try!

 

[WinDancer]

Haven't a clue why that worked- but it did.
Thank You!
Off to the next problem

 

[pbaldy]

The current event fires when you change records, so it keeps the buttons in sync with the record being displayed.

Glad we finally got it sorted out!

 

[alcapps]

you could use the open args style..
if you add the last argument to the docmd open form.
then in the opening form
in the OnOpen event
use
if Len(nz(me.OpenArgs,"")) > 0 then
set you value here
control = me.OpenArgs
'if you need to filter for one value then you use the same as vb filtering here
' me.filter = "your filter here"
' me.filteron = True
'end if
end if

Hope this helps