Pop-up box with question

[Thorson]

I currently have a table with records and form for entry to keep track of
animal's health records (tblTherapueticHealthRecords). I also have a table
that lists all the approved drugs for use on the animals (tblDrugs). Tbl
Drugs is used as a lookup for the "drug" field in
tblTherapueticHealthRecords. I would like it so that if the user wants to
enter in a new drug that was used into tblTherapueticHealthRecords or the
form, a box would pop-up asking if they would like to add the drug to the
database, then if they click yes a form opens asking them to provide the drug
information, if they click no it goes back to the table or form.

How do I do this? Do I use a Macro or VBA? I am not very experienced with
either.

 

[Clifford Bass]

Hi,

Very easy if you are using Access 2007. So, what version are you using?

Clifford Bass

 

[debora]

I currently have a table with records and form for entry to keep track of
animal's health records (tblTherapueticHealthRecords). I also have a table
that lists all the approved drugs for use on the animals (tblDrugs). Tbl
Drugs is used as a lookup for the "drug" field in
tblTherapueticHealthRecords. I would like it so that if the user wants to
enter in a new drug that was used into tblTherapueticHealthRecords or the
form, a box would pop-up asking if they would like to add the drug to the
database, then if they click yes a form opens asking them to provide the
drug
information, if they click no it goes back to the table or form.

How do I do this? Do I use a Macro or VBA? I am not very experienced
with
either.

 

[Thorson]

Right now I'm using Access 2003. I would like to try the trial version of
access 2007 to consider switching... but I can't get my database to convert
(which appears to be a common problem).

So the answer for right now is Access 2003

 

[Clifford Bass]

Hi,

To do it in 2003 or earlier, create a button on the form, in it's On
Click event open the code form in add and dialog mode. Then requery the
combo box after the user closed the code form. So for a combo box named
cbENTRANCE_TYPE_CODE on an entrances form the button might be named
cmdAddEntranceType and the code would look like this:

Private Sub cmdAddEntranceType_Click()

DoCmd.OpenForm "frmEntrance_Type_Codes", acNormal, , , acFormAdd, acDialog
[cbENTRANCE_TYPE_CODE].Requery

End Sub

As to the conversion issue. Does you database open up a form when it
is opened? If so, open it will holding the shift key down so that no form or
code runs. Then you should be able to do the conversion.

Hope that helps,

Clifford Bass

 

[Thorson]

It works great thanks!

As far as Access 2007, I read about how to convert it and I do have a form
that opens but I either hold the shift key or click F11 once open and then
close the form. The problem is when I actually try to convert the file to a
2007 it gives an error. There is actually a whole discussion about it on
this website if you have any advice to offer:

Converting access 2003 to 2007 erro
http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1

Hi,

To do it in 2003 or earlier, create a button on the form, in it's On
Click event open the code form in add and dialog mode. Then requery the
combo box after the user closed the code form. So for a combo box named
cbENTRANCE_TYPE_CODE on an entrances form the button might be named
cmdAddEntranceType and the code would look like this:

Private Sub cmdAddEntranceType_Click()

DoCmd.OpenForm "frmEntrance_Type_Codes", acNormal, , , acFormAdd, acDialog
[cbENTRANCE_TYPE_CODE].Requery

End Sub

As to the conversion issue. Does you database open up a form when it
is opened? If so, open it will holding the shift key down so that no form or
code runs. Then you should be able to do the conversion.

Hope that helps,

Clifford Bass

Right now I'm using Access 2003. I would like to try the trial version of
access 2007 to consider switching... but I can't get my database to convert
(which appears to be a common problem).

So the answer for right now is Access 2003

 

[Clifford Bass]

Hi,

You are welcome!

Once the form has opened, even if you close it, it will give the error.
Just to be absolutely sure on this, if you will bear with me: Navigate to
the database using your My Computer or Windows Explorer. Hold down the shift
key, and keeping it down, double-click on the database icon. Continue to
hold the shift key down until it is done opening Access and the database.
Now try doing the save as. If that does not work, you could try removing the
form from the startup option. And also, if you have an AutoExec macro,
temporarily rename it to something like AutoExecX so it will not run. Close
out, reopen and try the save as. If still nothing, you could always go the
route of creating a new 2007 database and importing all of the objects from
the existing one.

I would be interested to hear the results.

Clifford Bass

 

[Thorson]

I actually went back to the discussion that I told you about and someone
found a solution on there, It worked great. Now I will be using Access
2007.... I already set up the button though to add a new drug if the user
wants, is there a better solution using 2007? Or should I stick with what I
have?

Thanks for your help!

 

[Thorson]

So what I did was go to Access Options, then selected "none" for "Display
Form" in the Current Database tab, I then had to close and re-open the
program.

 

[Clifford Bass]

Hi,

Great! You are welcome.

You will now be able to specify the form in the combo box's List Items
Edit Form property. And you can get rid of the button and code if you wish.
It works a little different from the method I gave in that the user has to
type something into the combo box. If invalid it will prompt as to whether
or not the user wants to add a new item. Or the user can right-click on the
combo box and choose Edit List Items... I find this to be the better,
cleaner method. Also it allows for editing of the existing codes. Which may
or may not be fine, depending on your situation.

Good luck,

Clifford Bass

 

[Thorson]

So I put frmDrugList in the List Item's Edit Form Property for the combo box
and it comes up with a warning "You cannot add or change a record because a
related record is required in table tblDrugList"

I also changed the List Items Edit Form for the table the form is created
from to "frmDrugList"

Do I have to change something with my relationships? right now referential
integrity is requiring that a drug listed be listed in tblDrugList.

 

[Clifford Bass]

Hi,

No, keep the referential integrity as defined. It sounds like the
wrong column of the combo box is bound to the table column. Maybe the drug
name instead of the drug code? Bear with me if you have already done this.
For something like this you typically will use a query that returns a drug
code and a drug name for the combo box's Row Source. You would set the
Column Count to 2 and then the Bound Column to 1. If you do not want to
display the code, but rather the name, you can set the Column Widths property
to something like 0";2".

If that is not is, let me know.

Clifford Bass

 

[Thorson]

As far as that goes everything looks ok to me. the table that the form is
made from has a lookup of tblDrugList, the only drug identifier is the field
"DrugName". The look up in the table is a query of tblDrugList, the column
count is 1 and the bound column is 1. This is the same when I look at the
form.

Any thoughts?
I really appreciate your help.

 

[Clifford Bass]

Hi,

Yes. Make sure the Limit To List property of the combo box is set to
Yes.

Clifford Bass

 

[Thorson]

That was the problem. It works great now! Thank you so much for all your help

 

[Clifford Bass]

Hurray! You are much welcome!

Clifford Bass