Error 3061 Too few Parameters. Expected 1

In terms of the data returned whether you include the column from one side of the join or the other, the values will be the same.

Although I don't think its directly relevant in this case, as you've mentioned 'lookup fields' this is perhaps a good opportunity to discuss these.  The first thing to say is that when designing a table do not use the 'lookup wizard' to select the data type for a column.  For reasons why see:

http://www.mvps.org/access/lookupfields.htm

Similarly, if the column is a foreign key referencing the primary key of another column do not set its DisplayControl property to a combo box.  This has much the same effect as using the lookup wizard, though with not quite so many bad consequences.

If you have done either of those, though, don't worry too much  While not a good idea, they are not fatal, and you can still do as I describe below.

The easiest way to illustrate the salient points is probably with a simple example.  Say you have a table Orders which includes a CustomerID column.  Don't make this a 'lookup field', simply leave it as a long integer number.  This will reference a table Customers, with a primary key CustomerID probably an autonumber, and a text column Customer say.  If CustomerID in Orders had been defined as a 'lookup field' you could create a query based solely on orders which will show the Customer text value.  It's real value would be the relevant CustomerID numeric value, however.  This can be confusing.  What should be done is to join the two tables on CustomerID in a query and return the text Customer column from Customers.  This query could be used as the RecordSource for a report for instance.

When it comes to a form for entering order data, however, you should adopt a different approach.  In the form use a combo box bound to the CustomerID column from Orders and set it up as follows:

ControlSource:    CustomerID

RowSource:     SELECT CustomerID, Customer FROM Customers ORDER BY Customer;

BoundColumn:   1

ColumnCount:   2

ColumnWidths:  0cm;8cm

If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box.

You can now select the customer by name when entering data, but the underlying value of the combo box will be the CustomerID in the hidden column.

Let's say you also want to show other data from the Customers table in the form, e.g. address and phone number.  These data will be in columns in the Customers table.  In this case you should base the form on a query which joins Orders and Customers on CustomerID.  In the query return all the columns from Orders and only the address and phone number columns from Customers.  NB not the Customer column from Customers.

In the form as well as controls bound to the columns from Orders (including the above combo box for CustomerID) include text boxes bound to the address and phone number columns from customers,  Set the Locked property of these text boxes to True (Yes) and their Enabled property to False (No) to make them read-only.  When you select a customer in the combo box their address and phone number data will automatically show in the read-only text boxes.

In essence what's been done here is to transfer the 'lookup' functionality from the table (where it shouldn't be) to the form (where it should be), and as data should only be entered via forms, never in a table's raw datasheet view, this gives you all the advantages of a 'lookup field' without the problems associated with defining a column as such in the table design

Ken,

I read this and this is an excellent explanation of something that I didn't get a clear understanding about lookup fields in the table design.  I never used the wizard to create a lookup field but, I think from looking at the Northwind Database and other Microsoft databases in Access 97, May have seen it also in the Repair Service Calls database, make me guilty of using the  Lookup Table Design.

ControlSource:    CustomerID

RowSource:     SELECT CustomerID, Customer FROM Customers ORDER BY Customer;

BoundColumn:   1

ColumnCount:   2

ColumnWidths:  0cm;8cm

This example is also the same way I would show a combo value in a form.  So, I actually duplicate that SELECT statement and the other properties on bothe form and table.

I'll never use lookup on a table again.  Actually, I' never was sure if it was okay, because of the awkward results when I run a query and somettmes I see the CustomerID value and sometimes I see the Customer Name value.  I'm not sure if I should go back to the tables and remove the Lookup, but I think Access will update the forms affected by change in table design.

I did read the link about why you should not using lookup fields in a table, and again I read the last reason and I got scared when It mentioned nested queries.  Also slower performance in Reports based on queires with lookups.

Now, I understand why it is better to not set upa combo in table design.  You mentioned the form is fine.  I copied your example.  I do the same on the form.  But you taight me a good lesson acout creating a query as a form's recordsource and setting the address and phone nomber controls to read only by locking and disabling the controls.  They are the result from the query and I guess I wouldn't need Customer, because the combo box returns CustomerID and Customer, therefore, not required in the query.

I wish I had of known this before.  I saw the Northwind database and Service calls database that came with Access and they are culprits as well.

So this explains why I see Lookup values in queries.  I think if I never set up a lookup field in any table design, I should never see the i.e. Customer name value in a query.  So If I ever wanted to query the Customer Name in a query, this vaue should always come from tabl Customers, Customer Name.

It's clear now.  I even think I iread that along time ago in that link about the pitfalls of using lookup fields.  And you make it clear, by aying it is okay on form control, like a combo box.  As per your example as I pasted on this post.

Thanks for clarification and also advice about a query recordsource on a form with read only controls, based on query vslues, that shouldn't be edited on the form.

Thank you Ken,

Ron

Hello Ken,

I really don't know where this error 3131 is in the strSQL FROM clause.  I am stuck here.

A couple of nights ago I had this idea about using the Design grid to create another append query.  In case you hadn't read an earlier post, I did create a test table with the same structure as tblTransactions, I only renamed it, and then took the Union query that we are basing the code on and see if it would append to the Test table.  It did, except for the GLTransactionNo, which remained at 0 in all records.  That was the reason for the code for the strSQL.  Now that part works in code, but something else as tedious as a space or , or " or God knows what's wrong with it.  I'm getting off track, sorry.

Anyways, my idea was to try to create and append query, but use this line DMax("GLTransactionNo","tblGLTransactions') AS GLTransactionNo.    And repeat the same for UNION ALL SELECT statement.

So That's what I did and I tested it to append to the test table(same structure) and It worked.

I know I would have to place this append query immediately after thr first strSQL, in palce of the strSQL that you did ken.  Just a thought.  I'm using sTest Union query and Test table in my Test database,

Here is the union query for test purposes,

SELECT    qryYearEndReBalanceSheetTotals.AccountID, IIf([TotalTrans]<0,[TotalTrans]*-1,0) AS Debit, IIf([TotalTrans]>0,[TotalTrans],0) AS Credit,DMax("GLTransactionNo","tblGLTransactions") AS GLTransactionNo, "Closing Entry" AS Source, qryYearEndReBalanceSheetTotals.TransDate

FROM qryYearEndReBalanceSheetTotals

UNION SELECT ALL   ([Forms]![frmYrEndProcessing]![cboRetainedEarnings]) AS AccountID, IIf([SumOfProfit]>0,[SumOfProfit],0) AS Debit, IIf([SumOfProfit]<0,[SumOfProfit]*-1,0) AS Credit,DMax("GLTransactionNo","tblGLTransactions") AS GLTransactionNo, "Closing Entry" AS Source, DLookUp("FiscalYearEndDate","tblMyCompanyInfo") AS TransDate

FROM qryYearEndBalanceSheetProfit;

So I appended this on the Design Grid to tblTransactionsTest, viewed it,  It looked right.

Then I opened the frmYrEndProcessing to get the AccountID, ran the appendQuery, checked the Testtable and noticed it Picked up the last GLTansactionNo.

That number should change when thr first strSQL with tblGLTransaction runs and increases the GLTransaction No by 1.

If you think, this is okay, then this append query could run in a similar way as the last

queryto change the date runs, DBEngine(0)(0).Execute "qupdYearEndDateChanges", dbFailOnError

Except it would be in place of the strQry we have now.

 replace with DBEngne(0)9)).Execute "qappendName ofQuery", dbFailOnError

What do you think.  Maybe I will try it in th back test Database.

I don't know how to debug the strSql we have now, whick looks right.

Rob