Recent content by accessjack

Thanks PHV, this worked, great tip. I have two follow up questions. 1) The tblMatrix also has 12 balance fields in it. For the part of the code above (Line 3) where you say "SELECT * FROM tblMatrix..." would I be better off specifying fields? 2) I'm not very experience with nested...

PHV, Thanks for your post, the table alias trick really improves the readability of the code. You asked what happened...I got no error, just appends 0 records. Imagine this sample data already in tblMatrix: App Bank Account Year 1 1 1 2008 Now I want to add: App Bank Account...

I was able to add the composite primary key by doing the following: 1) Turn warnings off 2) Remove the full WHERE clause above 3) Turn warnings back on This way access by default will try to add duplicate primary key but the error message is suppressed. Here's sample data: App Bank...

Randy, I've double checked it in the debugger and the year appears to be correct. I'm pretty sure the where clause it missing parenthesis () but I'm not sure how to next them.

I have two tables: tblDeposits (App, Bank, Account) and tblMatrix (App, Bank, Account, Year, Amount) The composite primary key in tblDeposits is App + Bank + Account. The composite primary key in tblMatrix is App + Bank + Account + Year. I have a form where user chooses Year from a combo box...

Thanks pwise. Since ultimately the form in the external db was updating a table in the external database, by linking the tables, and importing the form, I was able to update the data in the external databases locally. Thanks.

I would like to use one database form to run an update in 4 external access databases. Master.mdb has 1 form called frmAutomation which has one combo box called cboPeriod. I would like to add a command button to this form adding vba code to the "on click" event. When clicked, the code will...

Worked great. Thank you.

To append new records from tblOne to tblTwo, I use an action append query to add records where the id field in tblOne does not exist in tblTwo. I am trying to do this using vba rather than an action query. Below is my code. The code compiles and executes but 0 records are appended (no error...

HarleyQuinn, It worked when I did it, but when I try to run the query a little later, it did revert back again. It's weird, it's like the changes keep when I save, close & rerun the query, but when I run the subsequent query which uses the 1st query above as it's source, it just changes the...

Thanks, it worked when I saved it in SQL view. I never would have thought of that.

Yes. I tried the bracket. I got the following error message: [Adj-01] is not a valid name. Make sure that is does not include invalid characters or punctuation and that it is not too long.

While in query design view, I am trying to rename a column/field name to Adj-01 (see formula below; This works fine, however, when I save and exit the query, and then go back in, access renames the column/field name to Adj-1. The "0" gets ommitted. Since I am using the precise field name in a...

Thanks Ginger, it worked. I modified slightly, I had to add brackets to the fields names as it seems access doesn't like it. For i = cbostart To cboend If i < 10 Then strSQl = strSQl & ", [0" & i & "]" Else strSQl = strSQl & ", [" & i & "]" End If Next i One...

Thanks RivetHead, This is close. I have 2 issues: For month for 2 digits 10, 11, & 12, the sql code makes them 010, 011, 012. Also, for some reason I keep getting an error on the line QDef.sql = strSQl or perhaps the next line. It says error in from clause. I used a breakpoint and the...