Array

[Ezekiël]

Hi,

Can someone help me with creating an array?

I would like to compare strings which are typed in a textbox with a list of
values stored in a seperate table. For example: if a number is typed in the
textbox like 123456, then after update it must lookup the value in another
table for if it exist. If it exist then allow the typed value to be saved,
if not a message or something else may appear.

How can i achieve this?

Greetings,

Ezekiël

 

[Michel Walsh]

Hi,


That is a data referential integrity case. In the Relationships window, add
the two tables, then, drag the field capturing the data onto the field of
reference, and enforce the relation. You won't then be able to enter data in
table1.field1 if it is not in tableOfReference.FieldOfReference (unless
field1 value Is Null).


Hoping it may help
Vanderghast, Access MVP

 

[John S]

I'll give you a rough outline. Create a method that makes an SQL call to
your table (select afield from a table where afield = '" & txtBox.value "
'".). If the recordset returned is null, process your data; if the recordset
returned is not null (i.e. you found a record) output message in
messagebox, and exit sub. You'll have to manipulate your data depending on
whether the info tested in your table is a number or a string.

Something I've not tried, but might be a simpler solution is use of the
Dlookup function and an ISnull function. Something like this(example from
Access book)

IbNull(DLookup("[afield]", "aTableName", "[afield] = txtbox.value " ))

You may have to play with this a bit, but essentially is the function
returns "true", undertake the save, and if not messagebox, exit sub, etc).

J. Smith
Aylmer, PQ Canada

 

[Ezekiël]

Hi Michel,

Thanks for your advice. However it is something different. The typed value
can be stored in the table but it must lookup the value in another table. If
the value exist then the typed value must be stored and if not e.g. an
status value will be stored in the textbox.