Form - Subform?

[kc]

I am creating a database and need some input forms. I have clients who live
in various different types of housing. I would like to have a dropdown list
to select the type of housing and then display the correct subform with the
right fields for the specified type. This is a one-to-one relationship, as
each client would only reside in one housing at a time. I chose to store the
different types of housing in separate tables because not only do they
require different types of info, but some are gourp facilities that house
several clients and I would like to store those addresses just once under a
facility ID.
I can get the proper subform to display by clicking on the item in the
dropdown (a simple visible = true/false), but I am having trouble with
getting the subform to work. I have it checking to see if a record exists in
the sub form:

If IsNull(DLookup("[Y_ID]", "Y_Residence", "[Y_ID] =" & current_Y_ID)) Then
Me![sfrm_Residence].SetFocus
Me![sfrm_Residence].Form.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
Me![sfrm_Residence].stext_Y_ID.Value = current_Y_ID
End If

At this point I am getting a "Data type mismatch in criteria expression."
error for my If statement.

Am I going about this all wrong? Is there a more graceful way of
accomplishing what I want to do?

Thanks for any assistance.

kc

 

[Golfinray]

In my experience, most of the time a data type mismatch means that you are
calling something text in one table and a number in another or date in one
table and currency in another. Something in your table is set to the wrong
data type or two items that are the same are on different data types. Go into
design view of your tables and check.

 

[kc]

I checked my data types and they are all text. Do you have any suggestions
for checking to see if a record already exists and if not creating a new one,
and if it does displaying the correct record?

In my experience, most of the time a data type mismatch means that you are
calling something text in one table and a number in another or date in one
table and currency in another. Something in your table is set to the wrong
data type or two items that are the same are on different data types. Go into
design view of your tables and check.

I am creating a database and need some input forms. I have clients who live
in various different types of housing. I would like to have a dropdown list
to select the type of housing and then display the correct subform with the
right fields for the specified type. This is a one-to-one relationship, as
each client would only reside in one housing at a time. I chose to store the
different types of housing in separate tables because not only do they
require different types of info, but some are gourp facilities that house
several clients and I would like to store those addresses just once under a
facility ID.
I can get the proper subform to display by clicking on the item in the
dropdown (a simple visible = true/false), but I am having trouble with
getting the subform to work. I have it checking to see if a record exists in
the sub form:

If IsNull(DLookup("[Y_ID]", "Y_Residence", "[Y_ID] =" & current_Y_ID)) Then
Me![sfrm_Residence].SetFocus
Me![sfrm_Residence].Form.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
Me![sfrm_Residence].stext_Y_ID.Value = current_Y_ID
End If

At this point I am getting a "Data type mismatch in criteria expression."
error for my If statement.

Am I going about this all wrong? Is there a more graceful way of
accomplishing what I want to do?

Thanks for any assistance.

kc