Passing Access constant as argument (1 Viewer)

Meltdown · May 26, 2016

Hi all,

Is it possible to pass an access constant as a argument to a function?

I have a button called Last with a sub on the click event that runs DoCmd.GoToRecord , , acLast

I want to replace that with a call to a Function like this =GoToRecord("acLast") -- passing in the access constant acFirst, acNext as required.

This is my code which doesn't work:

Code:

Private Function GoToRecord(strPosition As AccessObjectProperty)

 DoCmd.GoToRecord , , strPosition

End Function

Thanks
Melt

sneuberg · May 26, 2016

Try passing it as a long.

Meltdown · May 26, 2016

Sorry, no, that doesn't work.

The problem seems to be Access adding quotes or brackets around the argument, like this =GoToRecord([acLast]) or =GoToRecord("acLast") which are not the same as the Access constant.

I know I could write an if statement to check the passed value, but I was hoping to avoid that.

Regards
Melt

sneuberg · May 26, 2016

I put the following code in a form and when I click on the button Command17 it goes to the last record. Isn't this what you wanted to do?

Code:

Private Sub Command17_Click()
GoToRecord acLast
End Sub

Private Function GoToRecord(lngPosition As Long)

 DoCmd.GoToRecord , , lngPosition
 
End Function

jdraw · May 26, 2016

It might be easier for readers if you told us what and why you are suggesting this?
Did you try it? Result?
What does "doesn't work" mean?
You might try creating your own Enums, but a lot depends on the context (in my view).

Have you considered working with a recordset and using the MoveFirst, MoveLast, MoveNext methods?

Meltdown · May 26, 2016

No, I want to remove all the sub code from my navigation buttons and replace with the single GoToRecord function which is called from the OnClick event of the button using =GoToRecord("acLast").

The purpose is to clean up my code for simple navigation tasks. I can remove 5 subs for First, Next, Previous, Last, NewRec and replace with a single function.

Regards
Melt

sneuberg · May 26, 2016

Meltdown said:
I know I could write an if statement to check the passed value, but I was hoping to avoid that.

That does seem to work though. With

Code:

Public Function GoToRecord(strPosition As String)

Dim lngPosition As Long

If strPosition = "acLast" Then
    lngPosition = acLast
End If

DoCmd.GoToRecord , , lngPosition
 
End Function

in a module. This works

Code:

=GoToRecord("acLast")

Sorry about being so dense on this one.

Meltdown · May 26, 2016

Thanks sneuberg, that does work.

I just wanted to check if it could be done without parsing.

Thanks for your help

Regards
Melt

jdraw · May 26, 2016

There are intrinsic constants within MS Access. You should be able to access them without using a string and parsing (I think).

These can be found using the object browser (see jpg)

?acLast
3
?acPrevious
0
?acNext
1
?acFirst
2
?acNewrec
5

sneuberg · May 27, 2016

jdraw said:
There are intrinsic constants within MS Access. You should be able to access them without using a string and parsing (I think).

These can be found using the object browser (see jpg)

?acLast
3
?acPrevious
0
?acNext
1
?acFirst
2
?acNewrec
5

Yeah I thought that too but let's say you have a function in a module like

Code:

Public Function GoToRec(lngPosition As Long)

DoCmd.GoToRecord , , lngPosition
 
End Function

You can put

Code:

=GoToRec(3)

in an an on click event and it works, but if you try replacing the 3 with acLast you end up with

Code:

=GoToRec([acLast])

Access puts in the brackets. So apparently those constants are not recognized there. I suppose the OP could just use the numbers.

stopher · May 27, 2016

Afaik, Access has no knowledge of VBA constants. So if you try to use acLast in the form designer screens then Access will assume you are referring to a field name.

jdraw · May 27, 2016

I think stopher is correct. In my view the constants already existed so they could probably be used.
However, I was working on a post in the last few days, rejigging an older vb function to help a poster, and that code set up its own Enums (nearest, Up and Down). I know there are intrinsic constants for Up and Down, but the original creator of the code, that I was "rejigging", decided to use his own Enums. So, I'm now thinking that the intrinsic constants may have some scope limitations (perhaps only with the Access-originated objects --such as DoCmd, Recordset etc. )

I haven't found a definitive source.

Passing Access constant as argument (1 Viewer)

Meltdown

Registered User.

sneuberg

AWF VIP

Meltdown

Registered User.

sneuberg

AWF VIP

jdraw

Super Moderator

Meltdown

Registered User.

Attachments

sneuberg

AWF VIP

Meltdown

Registered User.

jdraw

Super Moderator

Attachments

sneuberg

AWF VIP

stopher

AWF VIP

jdraw

Super Moderator

Similar threads

Users who are viewing this thread