How do I get Update Query to prompt for update info?

  • Thread starter Thread starter Lyn
  • Start date Start date
L

Lyn

HI all,

I am not a very advanced user. I have 3 questions.

1) I would like to get an Update Query to prompt for the
info I want to Update. (Much like a Select Query
when ... LIKE [Enter State]... produces a pop-up
requesting the "State". Is this possible in Update
Query? And How?

2) Also, can two users have access to one database? At
the moment the first user in gets EDIT rights and the
other gets READ ONLY rights .... I would like them both to
EDIT??

3) My database has 11 tables. The main/parent table has
cascading updates set in relationships to all child
tables. When a new record (and ID no.)is created in the
main table I would like the other 10 tables to
automatically have a record of the same ID NO. created.
AT the moment I am individually creating records for each
related table. Is this possible?

Thanks
Lyn
 
Lyn said:
HI all,

I am not a very advanced user. I have 3 questions.

1) I would like to get an Update Query to prompt for the
info I want to Update. (Much like a Select Query
when ... LIKE [Enter State]... produces a pop-up
requesting the "State". Is this possible in Update
Query? And How?
Click to expand...

Lyn,

It works the same way as the SELECT Query, enclose some question in []
(that isn't a column, Table, or QueryDef name), and Access will prompt for
the information.

Example

CREATE TABLE Table1
(NameFirst TEXT(12)
)

Note: Primary Key deliberately left out for simplicity.

Sample Data:
SameName
SameName
SameName

UPDATE Table1
SET NameFirst = [Enter Name]

On Prompt, type: OtherName

Access will change all three rows to "OtherName".


2) Also, can two users have access to one database? At
the moment the first user in gets EDIT rights and the
other gets READ ONLY rights .... I would like them both to
EDIT??

3) My database has 11 tables. The main/parent table has
cascading updates set in relationships to all child
tables. When a new record (and ID no.)is created in the
main table I would like the other 10 tables to
automatically have a record of the same ID NO. created.
AT the moment I am individually creating records for each
related table. Is this possible?
Click to expand...

This one is a little tougher to answer.

The short answer is: Yes, it can be done.

The explanation of the short answer is: How depends on exactly what is
going on.

Access doesn't really have Triggers, like other RDBMS products. So, if
you're talking SQL/QueryDefs by themselves, then no. If you're willing to
use VBA, then you can coordinate many activities, either executing QueryDefs
in succession, or simply writing the SQL directly in the code. You can run
your own modules when you want to, or write functions/subs that act in
response to Events fired on Forms.


Sincerely,

Chris O.
 
Thanks Chris,

The answer you gave to my UPDATE query worked well. I
thought something had to come BEFORE the [] and had tried
everything I could think of. I did not think of just
putting the request in the [] alone!

The answer to my "create new records through all tables"
will take a bit of working out! But I will try.

Anything on getting more than one person to edit a
database at a time?

Cheers
Lyn
-----Original Message-----

HI all,

I am not a very advanced user. I have 3 questions.

1) I would like to get an Update Query to prompt for the
info I want to Update. (Much like a Select Query
when ... LIKE [Enter State]... produces a pop-up
requesting the "State". Is this possible in Update
Query? And How?
Click to expand...

Lyn,

It works the same way as the SELECT Query, enclose some question in []
(that isn't a column, Table, or QueryDef name), and Access will prompt for
the information.

Example

CREATE TABLE Table1
(NameFirst TEXT(12)
)

Note: Primary Key deliberately left out for simplicity.

Sample Data:
SameName
SameName
SameName

UPDATE Table1
SET NameFirst = [Enter Name]

On Prompt, type: OtherName

Access will change all three rows to "OtherName".


2) Also, can two users have access to one database? At
the moment the first user in gets EDIT rights and the
other gets READ ONLY rights .... I would like them both to
EDIT??

3) My database has 11 tables. The main/parent table has
cascading updates set in relationships to all child
tables. When a new record (and ID no.)is created in the
main table I would like the other 10 tables to
automatically have a record of the same ID NO. created.
AT the moment I am individually creating records for each
related table. Is this possible?
Click to expand...

This one is a little tougher to answer.

The short answer is: Yes, it can be done.

The explanation of the short answer is: How depends on exactly what is
going on.

Access doesn't really have Triggers, like other RDBMS products. So, if
you're talking SQL/QueryDefs by themselves, then no. If you're willing to
use VBA, then you can coordinate many activities, either executing QueryDefs
in succession, or simply writing the SQL directly in the code. You can run
your own modules when you want to, or write functions/subs that act in
response to Events fired on Forms.


Sincerely,

Chris O.
Thanks
Lyn
Click to expand...


.
Click to expand...
 
Back
Top