running update query on afterupdate

[Guest]

I am creating a form based on two tables.

tblStorageBoxInventory
=================
BoxNumber (PK)
storage date

tbl Matters Details
=================
BoxNumber ((foreign key))
change date

(there are other fields in both tables but that isn't related to the question)

I want to run a update query from the StorageBoxInventory form, so that when
a user types the date in [storage date] Access prompts the user for updating
all related fields in the tbl Matters Details.

I have a update query that prompts the user for the box number and date (SQL
below). How do I modify that to fit the form?

UPDATE [tbl Matters Details] SET [tbl Matters Details].[change date] = [what
date?]
WHERE ((([tbl Matters Details].[box number])=[What box number?]));

 

[Guest]

Let's say, for example purposes, your controls are named
txtBoxNum and txtChangeDate

Create a command button that will apply the update. In the Click even of
your update command button:

Dim strSQL As String

strSQL = "UPDATE [tbl Matters Details] SET [tbl Matters Details].[change
date] = #" & Me.txtChangeDate & "# WHERE [tbl Matters Details].[box number] =
" & Me.txtBoxNum;"

CurrentDb.Execute(strSQL), dbFailOnError

The above code assumes change date is a date/time field and box numer is a
numeric field. If box number is a text field, it needs to have this syntax:

strSQL = "UPDATE [tbl Matters Details] SET [tbl Matters Details].[change
date] = #" & Me.txtChangeDate & "# WHERE [tbl Matters Details].[box number] =
'" & Me.txtBoxNum & "'";"

 

[OfficeDev18 via AccessMonster.com]

UPDATE [tbl Matters Details] SET [tbl Matters Details].[change date] = [Forms]
![YourFormName]![storage dte].Value WHERE ((([tbl Matters Details].[box
number] = [Forms]![YourFormName]![box no].Value));

This assumes that you have a form named 'YourFormName' and textboxes on the
form called 'storage dte' and 'box no'. Of course, you will want to change
the SQL to reflect reality. Also, you will want to run the update query only
after you have updated both textboxes.

Hope this helps,

Sam

I am creating a form based on two tables.

tblStorageBoxInventory
=================
BoxNumber (PK)
storage date

tbl Matters Details
=================
BoxNumber ((foreign key))
change date

(there are other fields in both tables but that isn't related to the question)

I want to run a update query from the StorageBoxInventory form, so that when
a user types the date in [storage date] Access prompts the user for updating
all related fields in the tbl Matters Details.

I have a update query that prompts the user for the box number and date (SQL
below). How do I modify that to fit the form?

UPDATE [tbl Matters Details] SET [tbl Matters Details].[change date] = [what
date?]
WHERE ((([tbl Matters Details].[box number])=[What box number?]));