Solved Copying data from a form (1 Viewer)

[ZombeWalker71]

Sounds easy but struggling.

I have a form that displays the current number of records from a table using COUNT [TodayCount], which works absolutely fine.

I've created a table tblSKUCount to store this figure called [YesterdayCount], this just needs to be one row of data

Back on my form I then created a text box called [txtYestCount] that uses a Dlookup to bring in the value from the tblSKUCount.

I now want to press a command button in my form that will copy the [TodayCount] into the forms [txtYestCount] which then overwrites the value in the tables field
OR
it will overwrite the value directly in the table [YesterdayCount] and subsequently update the value in my form.

It sounds easy, but I just can't figure this one out.

 

[Minty]

You can always calculate this, so I don't see any obvious need to store it, and have to ensure it is accurate, and always updated.
It seems a lot of steps for something that is readily available with a simple DCount()

What purpose does it serve?

 

[ZombeWalker71]

You can always calculate this, so I don't see any obvious need to store it, and have to ensure it is accurate, and always updated.
It seems a lot of steps for something that is readily available with a simple DCount()

What purpose does it serve?

Hi Minty, each day I count the current number of skus using the DCount and I get that figure which works fine.

The next day, when I run a few queries, I need to run the DCount again, I know I will get a different figure as the system is constantly adding/deleting records.

Its just a quick visual check to show me everything is ok.

if it displays a count of 50000 and yesterdays count was 50500, that is acceptable.

If it displays a count of 126 compared with 50500, then I have a problem that needs investigating.

So I need to

 

[bastanu]

Maybe try something like this in the click event of your button:

CurrentDb.Execute "Update tblSKUCount Set YesterdayCount = " & Me.TodayCount,dbFailOnError
Me.Requery

Cheers,

 

[ZombeWalker71]

Maybe try something like this in the click event of your button:

CurrentDb.Execute "Update tblSKUCount Set YesterdayCount = " & Me.TodayCount,dbFailOnError
Me.Requery

Cheers,

Perfect, works just how I wanted it too, thank you!

 

[Pat Hartman]

You need three queries.
1. calc yesterday's value
2. calc today's value
3. Determine the difference between 1 and 2 and calc the percentage.

you can do this with one query with multiple sub queries or you can create three separate queries. Two to calculate the counts and the third to join the other two and calculate the difference. I find that breading something like this up in to individual pieces helps because I can validate each piece independently which is not possible when you use a query with subqueries.