Adding a prompt when opening a form

[Guest]

Access 2003
I'm not sure if this is possible or not so please let me know.
I currently open a form and am directed to the first record. (i know that
this can be changed to go to add new record too). What I want is to add a
step upon opening the form that asks which record to go to (ie record number
96 or add new record)
Please note that I have not created macros and am not familiar with SQL or
VBA so if the answer is in any of those, I will need some detailed
explanations

Thanks in advance
Sophie

 

[George Nicholson]

Sophie:
Well, the first thing is that Access does not have static record numbers.
While it is easy enough to "go to the 96th record according to how the
records are currently ordered", that is probably not a good idea since it
will eventually lead to problems when new records are added/deleted, if the
records are filtered, if the records are sorted differently, etc. The
current Rec #96 will not necessarily be the same as Rec #96 2 minutes from
now, much less tomorrow. Given that, it's best to avoid Rec #s as any kind
of meaningful reference. (If your users start assuming that "go to Rec #96"
will always take them to the same record, you will have a bunch of very
unhappy users on your hands with no way to "fix" things...).

So, ask yourself, how are my users most likely to want to navigate this
data? CustName, CustNumber, PO#?
With an answer to that question, a solution isn't that far away.

You might look at this article and see which method sounds closest to what
you have in mind:
http://support.microsoft.com/kb/287658/en-us
Four ways to move to a record from a Combo Box selection

While the above article doesn't specifically address the "prompt when
opening" part of your request, that aspect can be incorporated if you really
want/need it. However, having a "goto" box on the form itself (as the
article describes) has the advantage of being always available, rather than
as a separate prompt that's only seen when the form opens.

Post back and let us know how you'd like to proceed.

HTH,

 

[Guest]

I should mention that whe I said record # i actually was referring to a field
called RequestID number. People will be searching this way as it is the
final step of a three step process. Enter the request. come back and enter
actions. after implementation review actions and provide an evaluation.
After a request is entered a report is printed that shows a summary of the
request and its progress. People will have a printed report which shows the
request ID # and will need to go to that number to update the details.

I'll have a look at the link you gave to see if anything there will work.

Thanks
Sophie

 

[Guest]

Thanks George the combo box did the trick!! I set it to look up the Request
ID # field and it works well

Now for my next question...
I know I can set the for to 'edit' and it opens automatically at a new
record. I know I can also set it to no edit so that it opens to the first
record but still allows me to add records. What I want to know is can I set
it to go directly to a new record on opening, but still have access to the
other records using my new combo box OR is it easier to use the navigation
buttons (which I have created) and click on 'add new record' button

Thanks Sophie

 

[George Nicholson]

If you want to have your form open on a new record but still have all
records readily available, the easiest way would probably be to insert a
line of code in the Open event of your form:
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

by taking advantage of default arguments, you could shorten this to:
DoCmd.GoToRecord acNewRec