473,322 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Sql Query Question

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
3 1750
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: majsen | last post by:
Hi, I have problem running this query. It will time out for me... My database are small just about 200 members. I have a site for swaping appartments (rental). my query should look for match in...
8
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...
3
by: John Ortt | last post by:
> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record...
3
by: Ekqvist Marko | last post by:
Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
2
by: mmitchell_houston | last post by:
I'm working on a .NET project and I need a single query to return a result set from three related tables in Access 2003, and I'm having trouble getting the results I want. The details: ...
22
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...
3
by: Richard Hollenbeck | last post by:
I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.