By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Sql Query Question

P: n/a
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.


Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
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.


Nov 12 '05 #3

P: n/a
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.



Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.