Open Form using Form with MsgBox Error Response

[Liz]

Access 2007. I have an inventory database with a form for inventory
disposal. The query behind the form is written to only pull up current
items, not those already disposed of. It prompts the user to enter an asset
number.

I started creating a second form for the input of the asset number and a
button that when clicked on would either open the query or respond with a
message box explaining that the asset number had already been disposed of.

That's where I'm stuck. Can someone help with the coding or with a better
way of getting where I need to be?

Thanks

 

[Albert D. Kallal]

In the forms on-open event, simply go:

if me.Status = "disposed" then
msgbox "The item has been already disposed"
Cancel = true
end if

The above code will set cancel = true in the form disposal, and cancel =
true will prevent the form from loading after the msg is displayd....

Note that the form that opens the obove will compalin if you don't use error
handling

eg:

on error resume next
docmd.OpenForm "name of above form",,,"likey some sql filter here"

 

[Liz]

Albert,

The code works great, but I must have an incorrect setting on the form.
With the new code, when the item has been disposed, the form opens, but has
shows no data. The detail section is blank.

Do you have any ideas?

Thanks

 

[Liz]

Albert,

There is a subform on this form - would that be why it still opens?

 

[Liz]

Albert,

The message box does not display either. It just opens the empty form.

Thanks

 

[Albert D. Kallal]

Albert,

The code works great, but I must have an incorrect setting on the form.
With the new code, when the item has been disposed, the form opens, but
has
shows no data. The detail section is blank.

Do you have any ideas?

The assumption here is that you launching a form to be opened to one
particular record.

I assume you have some code like:


dim strID as string

strID = inputbox("please enter asset number to view")

docmd.OpenForm "frmAsset",,,"assetid = " & strID

The above assumes you have a field/column in the form called assetID and it
is a number type field. If the above is a text type field,then you need
quotes around the strID as

docmd.OpenForm "frmAsset",,,"assetid = '" & strID &"'"

The above would thus open the asset form to the ONE record that the user
requested. When the form loads, we have:

if me.Status = "disposed" then
msgbox "The item has been already disposed"
Cancel = true
end if

So, try the code to open the form:
dim strID as string

strID = inputbox("please enter asset number to view")

docmd.OpenForm "frmAsset",,,"assetid = " & strID

Of course change "frmAsset" to the name of your form, and assetID to the
name of the field in the form that has the asset id.

I would also make the form model (set this in the forms "other" tab of the
property sheet.

It not 100% clear how, when, where you are opening this form, but I am
assuming you have some button or form that prompts the user for the assetID
to view/edit/work on, and then you attempt to launch that form. The above
example uses the were clause, and it quite typical of how one would
prompt/ask a user BEFORE one attempts to open the form.

 

[Liz]

Albert,

Works great with one hitch. As long as the user enters an assetid that
exists it's good, but if a number is entered that is not an assetid in the
table, the form opens blank.

Is there another if that can check that the strID exists in the table and if
not delivers a message box?

I really appreciate your time.
Thanks,
Liz

 

[Albert D. Kallal]

Albert,

Works great with one hitch. As long as the user enters an assetid that
exists it's good, but if a number is entered that is not an assetid in the
table, the form opens blank.

Ah, ok...so, no value at all.....

You can add the following in the forms on-load

If IsNull(Me.ID) = True Then
MsgBox "record not found"
Cancel = True
End If

if me.Status = "disposed" then
msgbox "The item has been already disposed"
Cancel = true
end if

 

[Liz]

I added it, but it's still brings up a blank form.

 

[Liz]

Albert,

This form's recordsource is a query. When I run it using the query I end up
with a blank form when entering a asset ID that is not in the table. I
tested the code with the form using a table and it worked. Is there any way
to work around that?

Thanks,
Lisa

 

[Liz]

I think I figured it out. The form and the subform are linked by the assetID
number, so there were two controls called assetID, one with a recordsource
that was a query and the other from a table. I renamed the one I didn't need
to use for the Null check and it works fine.

All of your help is very much appreciated.
Thanks,
Liz