Compare Current record to other Records 1

[OU18]

All,

I am looking to create a query or code that will do the following.

I have a table that has the following fields

table - tblInvoiceTransactions
BAN
AccrualMonth
InvoiceDate
ApprovedforAP

For each Accrual month there can be multiple entries

IE

Ban Accural Month InvDate ApprovedAP
12345 01/01/01 01/15/01 Y
12345 01/01/01 01/15/01 N
12345 01/01/01 01/15/01 N
12345 02/01/01 02/01/01 N

There is other data that makes each of these unique. What I need to create is something that will take the current record when ApprovedAP is set to Y and look and find the all the subsequent records with the same Accural Month, InvDate and BAN and update the ApprovedAP to Y. This will help avoid users fogetting to change the other associated line items to yes.

I hope i have explained this clearly enough. Any help with this is greatly appreciated.

Steve

 

[Tranman]

Steve,
It's a shame that MicroSoft didn't include the ability to create triggers in Access. That's what you really need.

Is it safe to assume that ApprovedAP is being set to "Y" on a form? If so, you should be able to stick some SQL in the AfterUpdate event for the ApprovedAP control like:

If me.<ApprovedAPtextboxname>.text = "Y" then
currentdb.execute "Update <tablename> set ApprovedAP = 'Y' where [Accural Month] = #" & <AMtextbox name> "# and InvDate = #" & <IDtextboxname> "# and BAN = '" & <BANtextboxname> & "'"

If you're not doing this on a form, all bets are off...

Good luck,
Tranman

 

[Tranman]

Oops, forgot the End If...

Also, you'd be updating the field you just updated, but that shouldn't really matter.

Tranman

 

[OU18]

IT is being done within a form. Thank you for your input. I will give this a shot when i get into work tommmorow. I appreciate the help

 

[OU18]

Here is what I typed in and it is giving me an error message

Error Message - Expected end of statement

If Me.ApprovedforAP = -1 Then
CurrentDb.Execute "Update tblInvoiceTransations set ApprovedforAP = -1 where [AccuralMonth]=#"&AccuralMonth"# and [InvoiceDate]= #"& InvoiceDate"# and [BAN]='"& BAN&"'"
End If

what could i be doing wrong. The expected end of statement is highlighting the "# and [InvoiceDate]= #" spot

Thanks for the help

 

[RoyVidar]

Hi!

There are some missing ampersands there, try:

[tt]...[AccuralMonth]=#" & AccuralMonth & "# and [InvoiceDate]= #" & InvoiceDate & "# and [BAN]='"& BAN & "'"[/tt]

Roy-Vidar

 

[OU18]

Thank you very much Roy, I also had no idea how important the spaces were in the code.

I appreciate the help very much

 

[OU18]

I have been trying to get this code to work in my database and it wont perform the desired action.

I keep getting error messages about data mismatch. When it does work it updates the entire table. Not the Accrual Month, Ban and Invoice Date of the associated records that was just changed.

Any insight as to what I may be doing incorrectly

I have entered the code as follows

If Me.ApprovedforAP = -1 Then
CurrentDb.Execute "Update tblInvoiceTransations set ApprovedforAP = -1 where [AccuralMonth]=#"&AccuralMonth&"# and [InvoiceDate]= #"& InvoiceDate&"# and [BAN]='"& BAN&"'"
End If

Thanks for all the help

 

[Tranman]

Steve,
I don't know why you would be getting data mismatches unless you have spaces in your textboxes or something like that.

My guess is that the reason your whole table is getting updated is that your textboxes on your form appear to have the same names as the fields in your table. In other words, the sql statement is just comparing the fields to themselves. (The same effect as if you said "Update <table> set <field> = 'XYZ' where field1 = field1") Obviously field1 = field1, so all rows will always be updated.

For the record, the braces(or are they brackets?) These things: [ ], do not tell sql that you are talking about a field in a table, they just allow you to use field names that contain space characters.

I'd rename the textboxes something like txtInvoiceDate, txtBAN; etc. then in the sql say "... where InvoiceDate = txtInvoiceDate and BAN = txtBAN..." and so forth.

Tranman

 

[OU18]

Good suggestion, I appreciate your help, i will give it a shot.

 

[OU18]

That completely did the trick. Just renaming the box on the form fixed it.

Thank you very much.

 

[OU18]

is it possible to do a similar update from a form, but have it populate a field in a table with information from another table.

IE.

All data is entered into a form.
all records in tblInvoiceTransactions have a GLAccountNumber associated to an Accrual Month.

The information for the appropriate GLAccountNumber is stored in tblGLAccrual.

Is it possible to have the system do the following:

when the user tabs out of the AccrualMonth field in the form, use the LostFocus event to update the GLAccountNumber field with the correct information from tblGLAccrual where tblInvoiceTransactions.[AccrualMonth]= tblGLAccrual.[AccrualMonth]

tblGLAccrual contains the following fields
GLAccountNumber - text field
AccrualMonth - Date/Time field


thanks in advance

 

[Tranman]

OU18,
Sorry I've been out of touch. The short answer to your quesion is yes, you can update a table with data from another table based upon some event.

The easiest way to do it is probably to create a recordset to fetch the information from the first table, then use the execute method like you did above to update the second table.

I don't quite understand the structure of your tables, or the task at hand, but I will show you how to fetch info from a table into a recordset, then update another table with the data you fetched. It will be up to you to fill in your own field names; etc.

So, here we go--In the event procedure you want to kick off the update:

Dim rs as New ADODB.Recordset

rs.Open "Select * from tblGLAccrual where AccrualMonth = txtAccrualMonth", currentproject.connection, adOpenDynamic, adLockOptimistic

'This creates a recordset object with all rows from tblGLAccrual where AccrualMonth is equal to txtAccrualMonth (which is assumed to be a field on your form)

CurrentDB.Execute "Update tblInvoiceTransactions set GLAccountNumber = '" & rs.Fields("GLAccountNumber") & "' where tblInvoiceTransactions.[AccrualMonth] = '" & rs.Fields("AccrualMonth") & "'"

'God knows why you would use the AccrualMonth from the recordset as a criterion, when you could just use the one from the form, but I did it just to show you how

rs.Close
Set rs = Nothing

'Should you ever want to, you can navigate through one of these recordsets by using methods like rs.MoveNext, rs.MoveFirst; etc.

'Any changes you make to the recordset object will be reflected in the underlying table, so they are a pretty powerful tool

Like I said, this will probably not do the job for you as I'm unsure about how you decide which account to fetch from tblGLAccrual, but at least it illustrates how to create a recordset and use the value in one of its fields to update a field in a table.

If you need some more help to get this to work, write back and explain how you decide which account to look up in tblGLAccrual, and where you get the AccrualMonth. Just go ahead and assume I don't know anything about your app, because I really don't, but I know quite a bit about how to do stuff.

As my time allows, I'll hang with you until this works like you need it to.

Tranman

 

[OU18]

Tranman, I think you are on the right path. Here is the table structure. Granted it is probably not the best way to represent it, but I am a programming novice.

In tblTransactions I have the following fields (these are the only relevant ones for this post)

TransactionID
AccrualMonth
GLAccount#

I also have a table called tblAccrualConversions that contains the following information

ID - AutoNumber
GLAccountNumber - Text
AccrualMonth - Date/Time

In my form frmTransactions, on the LostFocus event, I have an update query that runs and updates GLAccountNumber field in the form with the appropriate value from the TblAccrualConversion based on the AccrualMonth

So tblTransactions.AccrualMonth to tblAccrualConversions.AccrualMonth.

I have the criteria in the update query set to Is Null in the GLAccountNumber field.

The biggest problem I am running into is, the query is very slow, because it is looking at the entire 50,000 records. I would like it to look at just the current record the user is on, and update only that line item.

I thought the previous post would help do that?
Am I wrong.

Does the information above, help with the table structures.

 

[OU18]

Actually Tranman, I took some of what you wrote above, and just started messing around with it, and voila. It worked. I cant believe it.

Thanks for all the help though. Everything you have written in here has helped me do something.

 

[Tranman]

Steve,
Glad my help helped. Once you get past the frustration of not knowing how to do things and start seeing what's possible, this can be a lot of fun.

Good luck with your project.

Tranman (Paul)

 

[OU18]

Well everything you put in the post is great. As soon as I put the test on the network. It slowed to an absolute crawl. It takes about 26 seconds just to run an update query. I dont think it is working the way i want it to. So maybe you can still shed some light on it.

I only want it to update the record the user is currently on in the form with the appropriate GLAccountNumber. It seems as though it is looking through out the entire database still.

Thanks
Steve

And you are correct, the more I work with it the more fun it is to get it to do things.

 

[Tranman]

Steve,
26 seconds does seem to be a bit excessive. I've got it beat, though. I used to have a single update query that took 27 hours over the network. I'd kick it off in the morning, and it would finish just before lunch the next day. Needless to say, I did some serious work to it. Got it down to about 45 minutes, which seems like eternity, but considering what it's doing, is not really all that bad.

Why don't you post your code, and I'll have a look, and maybe get a better idea what you're doing?

Paul
(We probably know each other well enough to be on a first name basis )

 

[OU18]

Okay I will do that, mine is Steve

 

[OU18]

here is the code from the update query

UPDATE tblInvoiceTransations INNER JOIN tblAccrualConversions ON tblInvoiceTransations.AccuralMonth = tblAccrualConversions.[Accrual Month] SET tblInvoiceTransations.[GLAccount#] = [GL Account Number]
WHERE (((tblInvoiceTransations.[GLAccount#]) Is Null));

It fires off when the user Loses Focus on the txtAccrualMonth field in the form frmInvoiceTransactions (subform).

Like I said above, it seems as though it is looking at the entire tblInvoiceTransactions table and trying to update all the records.

Is it possible to pass the transactionID (AutoNumber assigned to each transaction entered) from the form to the query, and only update the current record the user is on. Hopefully that makes sense.

Thanks Paul.

I cant tell you how much I appreciate you letting me pick your brain.

Also, That is quite a long time to be waiting on an update query. But congrats on cutting out 26 hours 15 minutes worth of wait time.

Steve