Problem Entering category data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone and happy new year
I have a table with three primary keys; BaseID, Activity and Activity subtype
BaseID is a foreign key to a table with names
Activity is a foreign key to a lookup trable with activities
ActivitySubType is a Foreign key to a lookup table with specified activities when "other" is choosen in

Activity.

My problem is that I in the form want ActivitySubType to be dynamic. If the subtype is not in the list I

want it added automaticly. I have made it elsewhere where i make a query that reads the collumn and

makes a distinctselect to choose from. If I then ask my form to me.refresh I will have the new category

to choose from instantly.

Since my Primary Key collumn is a foreign key it need to be a number, I do not want my users to see this

number, I want them to see the category... If they then enter a text I get the error "not in list" I try

to select "No in "limit to list". Acces do not allow this since the key is not the first visible collumn
I would like my field to look up the value in my lookuptable, if its not there it should just add a new

category and make it instantly available AND store this new value in my ActivitySubtypefield.

How can I do that?

Best wishes
Reno Lindberg
 
-----Original Message-----
Hello everyone and happy new year
I have a table with three primary keys; BaseID, Activity and Activity subtype
BaseID is a foreign key to a table with names
Activity is a foreign key to a lookup trable with activities
ActivitySubType is a Foreign key to a lookup table with
Click to expand...
specified activities when "other" is choosen in
Activity.

My problem is that I in the form want ActivitySubType to
Click to expand...
be dynamic. If the subtype is not in the list I
want it added automaticly. I have made it elsewhere where
Click to expand...
i make a query that reads the collumn and
makes a distinctselect to choose from. If I then ask my
Click to expand...
form to me.refresh I will have the new category
to choose from instantly.

Since my Primary Key collumn is a foreign key it need to
Click to expand...
be a number, I do not want my users to see this
number, I want them to see the category... If they then
Click to expand...
enter a text I get the error "not in list" I try
to select "No in "limit to list". Acces do not allow this
Click to expand...
since the key is not the first visible collumn
I would like my field to look up the value in my
Click to expand...
lookuptable, if its not there it should just add a new
category and make it instantly available AND store this
Click to expand...
new value in my ActivitySubtypefield.
How can I do that?

Best wishes
Reno Lindberg
.
Click to expand...
Hi Reno, use online help for information of NotInList
event. Use following as an example...

private sub cboActivity_NotInList(NewData,Response)
dim rst as recordset

if msgbox(newdata & "... not in list, add
it?",vbokcancel,"New Activity")=vbok then
set rst=....
with rst
.addnew
.fields("Activity")=newdata
.update
.close
end with
set rst=nothing
response=acdataerradded
else
response=acdataerrcontinue
end if
end sub

Luck
Jonathan
 
Back
Top