You should use a query to get the Invoice Date to be displayed. Also, save
looping for last resort in any case - chances are high that you can always
accomplish it with a sql statement call.
For instance, in code you do this... (watch for word wrapping)
Me.txtInvDate = CurrentProject.Connection.Execute("Select IDate from Table1
INNER JOIN Table2 ON Table1.DID = Table2.ID Where Table2.ID = " & Me.IID &
"").Fields(0).Value
The "CurrentProject.Connection.Execute" is a hot connection to your data and
allows you to execute sql syntax.
The ".Fields(0).Value" is asking for the value of field 0 (which is IDate)
and assigns back to a text field on the form.
This is a standard ADO connection method call, very simple to do.
Now... that said... ideally (I think... in your situation), your form
RecordSource property should be a query that gives you the IDate field.
Something like...
SELECT *
FROM Table1 INNER JOIN Table2 ON Table1.DID = Table2.ID;
--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
"JC Mugs" <jc****@hotmail.com> wrote in message
news:vv************@corp.supernews.com...
Sorry, Mistyped the explanation,
The form & Table use IDate for "Invoice Date" As for relationship, only
that the DDate field will be imported(????) into the Form Record.. I am
Hoping to use a technique to lookup and update the field with no user
intervention. I thought a SQL procedure, would do it.
"Larry Linson" <bo*****@localhost.not> wrote in message
news:mD******************@nwrddc01.gnilink.net... Is there a relationship between Table 1 and Table 1? If so, follow up
here.
First thing... don't use "Date" as a Field name -- it is an Access
reserved word, and sooner or later, it's going to cause confusion that will be
very puzzling.
If not, then put a ComboBox on the Form, using Table 1 as the RowSource,
with the Date field in Table 2 as the Control Source. Use the Wizard to
make it easy to build and you can show whichever field will be meaningful to
the user, and both fields in the dropdown list.
Larry Linson
Microsoft Access MVP
"JC Mugs" <jc****@hotmail.com> wrote in message
news:vv************@corp.supernews.com... Help needed for project-Access 2002(office xp)
PROBLEM: Figuring out how to lookup a record and DDate field in
Table1 - Take that DDate-field data from record looked up and assign it to Date
field in a new record on a form using Table2.
BASIC
Table1
Fields: DID -Primary KEY
DDate --Information I need to move to new form
The Form uses :
Table2
Fields: IID -Autonumber Primary Key
ID
Date- -Where I need to move the data to
Name
Street
ECT-
I have the form that uses Table 2- It lookups the name,street etc from
a customer file (Table3, but not related to this problem), The form is
an invoice entry and the invoices are written sometimes days in advance..
So I was going to write the date to a table and access it from a lookup on
each form. (Also it will help me access other data in other tables by learning how to do this)
Any help is appreciated.. Also is there a good reference for
understanding how to get to data generated by a query, forms and tables? I have 3
reference books including Access 2002 Bible,How to do Everything with
Access 2002, and Microsoft Access Inside Out-2002. None of these really have any kind of simple and direct explaination of how to do this..
I haven't really used the visual programming, Old Basic programs seem
soooo... much easier to control-program.