Help with On_Click() command for Access 2010 Form

[jlwmkkp]

In my database, I have a table named "DataTable" with the following four fields: [Database_ID], [Flex_ID], [Last_Updated], [Owner].

[Database_ID] is used to link tables/queries/forms and is static. [Flex_ID] is used by the company, and is the ID that appears on all forms.

[Last_Updated] is a date/time field that allows me to pull the most recent record for each[Database_ID] value. For tracking purposes, I am not updating existing rows, but rather, adding new rows with modified data.

Occasionally, the [Flex_ID]s for two [Database_ID]s will swap (ie. After a swap between ([Database_ID] = 1 and [Flex_ID] = Alpha) and ([Database_ID] = 2 and [Flex_ID] = Bravo), the new values are ([Database_ID] = 1 and [Flex_ID] = Bravo) and ([Database_ID] = 2 and [Flex_ID] = Alpha).

In order to facilitate this swap, I would like to create a form with three controls: An input box for each[Flex_ID] to be swapped, and a button to run the swap.

For the above example, believe that the On_Click command for the button needs to do the following:

1. Copy the most recent record for each of the [Database_ID]s corresponding to the [Flex_ID]s with [Upload_Date] changed to =Date()
2. For [Database_ID] = 1, change [Flex_ID] in the most recent record to DummyID
3. For [Database_ID] = 2, change [Flex_ID] in the most recent record to Alpha
4. For [Database_ID] = 1, change [Flex_ID] in the most recent record to Bravo

 

[DavidAtWork]

I assume the reason why you'd want to do what you're suggesting is so that at any point in time you can track changes to the swap of the FlexID's
But in your steps above, you state you want to change the FlexID's implying you want to update records, in this case you wouldn't have a history of changes only the current Flex state and when it was last changed.
BTW your step 2 and 4 are changing the same record, why do you need to change [Flex_ID] to DummyID before Bravo.
I'm assuming you want to add records to record the Flex swaps, are there only ever 2 values for FlexID either Alpha or Bravo
Unless your sql is pretty tasty, you'd need to go through several steps to achieve the swaps in FlexID. You have to get the Max Upload_date for each database (GroupBy databaseID) and then use that as your criteria for the records you want to insert.
If you run this first step as a make table query, call it myMaxValues then you can base your append query on [DataTable] which will be linked to the make table, joining Database_ID = Database_ID and [Last_Updated] = MaxOfLast_Updated.
Your append sql will be
strSQL = "INSERT INTO [DataTable]([Database_ID], [Flex_ID], [Last_Updated], [Owner])
SELECT Database_ID, IIf([Flex_ID] = 'Alpha','Bravo','Alpha') as theFlex, Date() as theDate, 'The Owner'
FROM [DataTable] DT
INNER JOIN myMaxValues MV
ON MV.Database_ID = DT.Database_ID
AND MV.MaxOfLast_Updated = DT.Last_Updated"

This would all operate within the On_Click() event of your button, use the Docmd.RunSQL(strSQL) to run the above sql.
I don't see the need for any FlexID input boxes unless there are more than 2 values 'Alpha','Bravo'

David