Lookup feature in a form - design issue

  • Thread starter Thread starter Amit
  • Start date Start date
A

Amit

Hi,

I have several forms to add data/records. Most of them
have a lookup feature in the footer (eg. on the Project
form, you can enter a new project info, view existing
projects and in the footer, you can lookup a project using
a combo box that lists all the projects).

This form is opened through a menu, and also through
another form (Program form) just to add NEW records. When
I open the form through another form, and try to use the
lookup feature, it gives an error- "No Current Record".
Which makes sense.

I was wondering if there is a way around this. Should I
disable the lookup feature if the form is opened only to
add a new record? Can I open a form in edit mode to
display all the records AND have it go to a new record?
What code would I use? ('AddNew'??)

Thanks!

-Amit
 
Kelvin,

I tried this in the code behind the button that opens the
form, and it didn't work. I am passing args to the form
being opened through OpenForm. Will that make a
difference? Here's the code (in the OnClick event for the
button):

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![ProgramID]
DoCmd.GoToRecord , , acNewRec

Didn't work. I get a message that says "You can't go to
the specified record". This is not the VB run-time
message, but MS Access message. Then, the form opens to a
new record without showing the existing records.

-Amit
 
Why are you passing an argument? Using acFormAdd will open the form in data
entry mode to add new records. Since you should already be at a new record,
the second line of code will not work. Try

DoCmd.OpenForm stDocName, , , , acFormAdd

If you are trying to filter to a specific record then use the where criteria

stCriteria = "[Field1] = " & Me.[ProgramID]
DoCmd.OpenForm stDocName, , , stCriteria

If you want to be able to see the other records then just open the form
normally then use the goto command.

DoCmd.OpenForm stDocName
DoCmd.GotoRecord, , AcNewRec

Your lookup function will still work with this option. If you want the
lookup function for the second option you will need to remove the filter and
do a requery before running the FindFirst procedure. I assume that is what
you are using to find the record. The lookup won't work with the first
option so you probably should set the visiblity of the lookup box off.

Kelvin

Amit said:
Kelvin,

I tried this in the code behind the button that opens the
form, and it didn't work. I am passing args to the form
being opened through OpenForm. Will that make a
difference? Here's the code (in the OnClick event for the
button):

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![ProgramID]
DoCmd.GoToRecord , , acNewRec

Didn't work. I get a message that says "You can't go to
the specified record". This is not the VB run-time
message, but MS Access message. Then, the form opens to a
new record without showing the existing records.

-Amit
-----Original Message-----
To go to a new record you can use

DoCmd.GotoRecord, ,AcNewRec

Kelvin




.
Click to expand...
Click to expand...
 
-----Original Message-----
Why are you passing an argument?
Click to expand...

Because I want to set/select the value of the combobox in
the form (formB) that's being opened, using the value
selected in the form (formA) that has the button to open
formB. The way formB is being used, it can be opened in
two different ways -
a. directly (w/o a button) and the user selects the value
in the combobox; or
b. by clicking a button in formA, with the combobox value
passed to it - which means it has to open at a new record.
[Is there a simpler way to do this?]

The lookup feature makes it a little complicated, as it
doesn't work in b. So, I was trying to figure out if I can
have the same functionality in both instances (a. and b.)
using the same form.

It might be simpler to use two versions of formB - modify
formB slightly (take out the lookup feature from the
footer) and use that for b., and the original for a.

Again, I'm new at Access/VBA and still learning as to what
works and what doesn't. If I'm missing something simple
here, please do let me know.

Thanks for all your help.

-Amit
Using acFormAdd will open the form in data
entry mode to add new records. Since you should already be at a new record,
the second line of code will not work. Try

DoCmd.OpenForm stDocName, , , , acFormAdd

If you are trying to filter to a specific record then use the where criteria

stCriteria = "[Field1] = " & Me.[ProgramID]
DoCmd.OpenForm stDocName, , , stCriteria

If you want to be able to see the other records then just open the form
normally then use the goto command.

DoCmd.OpenForm stDocName
DoCmd.GotoRecord, , AcNewRec

Your lookup function will still work with this option. If you want the
lookup function for the second option you will need to remove the filter and
do a requery before running the FindFirst procedure. I assume that is what
you are using to find the record. The lookup won't work with the first
option so you probably should set the visiblity of the lookup box off.

Kelvin

Kelvin,

I tried this in the code behind the button that opens the
form, and it didn't work. I am passing args to the form
being opened through OpenForm. Will that make a
difference? Here's the code (in the OnClick event for the
button):

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me! [ProgramID]
DoCmd.GoToRecord , , acNewRec

Didn't work. I get a message that says "You can't go to
the specified record". This is not the VB run-time
message, but MS Access message. Then, the form opens to a
new record without showing the existing records.

-Amit
-----Original Message-----
To go to a new record you can use

DoCmd.GotoRecord, ,AcNewRec

Kelvin

Hi,

I have several forms to add data/records. Most of them
have a lookup feature in the footer (eg. on the Project
form, you can enter a new project info, view existing
projects and in the footer, you can lookup a project using
a combo box that lists all the projects).

This form is opened through a menu, and also through
another form (Program form) just to add NEW records. When
I open the form through another form, and try to use the
lookup feature, it gives an error- "No Current Record".
Which makes sense.

I was wondering if there is a way around this. Should I
disable the lookup feature if the form is opened only to
add a new record? Can I open a form in edit mode to
display all the records AND have it go to a new record?
What code would I use? ('AddNew'??)

Thanks!

-Amit


.
Click to expand...
Click to expand...


.
Click to expand...
 
Amit,

Try opening the form normally, then setting the value of
your combo box.

Forms!formname!fieldName=yourValue.

If your taking a value from a field on the currently open
form, its something like

Forms!openedFormName!fieldName=Me!fieldName.

I am assuming with this last statement your opening this
other form from a currently active form.

Hope this helps!

Kevin
-----Original Message-----
-----Original Message-----
Why are you passing an argument?
Click to expand...

Because I want to set/select the value of the combobox in
the form (formB) that's being opened, using the value
selected in the form (formA) that has the button to open
formB. The way formB is being used, it can be opened in
two different ways -
a. directly (w/o a button) and the user selects the value
in the combobox; or
b. by clicking a button in formA, with the combobox value
passed to it - which means it has to open at a new record.
[Is there a simpler way to do this?]

The lookup feature makes it a little complicated, as it
doesn't work in b. So, I was trying to figure out if I can
have the same functionality in both instances (a. and b.)
using the same form.

It might be simpler to use two versions of formB - modify
formB slightly (take out the lookup feature from the
footer) and use that for b., and the original for a.

Again, I'm new at Access/VBA and still learning as to what
works and what doesn't. If I'm missing something simple
here, please do let me know.

Thanks for all your help.

-Amit
Using acFormAdd will open the form in data
entry mode to add new records. Since you should already be at a new record,
the second line of code will not work. Try

DoCmd.OpenForm stDocName, , , , acFormAdd

If you are trying to filter to a specific record then
Click to expand...
use
the where criteria
stCriteria = "[Field1] = " & Me.[ProgramID]
DoCmd.OpenForm stDocName, , , stCriteria

If you want to be able to see the other records then
Click to expand...
just
open the form
normally then use the goto command.

DoCmd.OpenForm stDocName
DoCmd.GotoRecord, , AcNewRec

Your lookup function will still work with this option. If you want the
lookup function for the second option you will need to remove the filter and
do a requery before running the FindFirst procedure. I assume that is what
you are using to find the record. The lookup won't work with the first
option so you probably should set the visiblity of the lookup box off.

Kelvin

Kelvin,

I tried this in the code behind the button that opens the
form, and it didn't work. I am passing args to the form
being opened through OpenForm. Will that make a
difference? Here's the code (in the OnClick event for the
button):

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me! [ProgramID]
DoCmd.GoToRecord , , acNewRec

Didn't work. I get a message that says "You can't go to
the specified record". This is not the VB run-time
message, but MS Access message. Then, the form opens
Click to expand...
Click to expand...
to
a
Click to expand...
use
the


.
Click to expand...
.
Click to expand...
 
Back
Top