Sql Query Question

**Larry Linson** · Nov 12 '05, 06:29 PM

Re: Sql Query Question

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" <jcmugs@hotmail .com> wrote in message
news:vvgsglq7rc o7b7@corp.super news.com...[color=blue]
> 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[/color]
field[color=blue]
> 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[/color]
I[color=blue]
> 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[/color]
understanding[color=blue]
> 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[/color]
Access[color=blue]
> 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.
>
>
>
>[/color]

**JC Mugs** · Nov 12 '05, 06:29 PM

Re: Sql Query Question

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" <bouncer@localh ost.not> wrote in message
news:mD_Jb.3172 8$nK2.6899@nwrd dc01.gnilink.ne t...[color=blue]
> Is there a relationship between Table 1 and Table 1? If so, follow up[/color]
here.[color=blue]
>
> First thing... don't use "Date" as a Field name -- it is an Access[/color]
reserved[color=blue]
> 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[/color]
make[color=blue]
> it easy to build and you can show whichever field will be meaningful to[/color]
the[color=blue]
> user, and both fields in the dropdown list.
>
> Larry Linson
> Microsoft Access MVP
>
>
> "JC Mugs" <jcmugs@hotmail .com> wrote in message
> news:vvgsglq7rc o7b7@corp.super news.com...[color=green]
> > 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[/color]
> field[color=green]
> > 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..[/color][/color]
So[color=blue]
> I[color=green]
> > was going to write the date to a table and access it from a lookup on[/color][/color]
each[color=blue][color=green]
> > form. (Also it will help me access other data in other tables by[/color][/color]
learning[color=blue][color=green]
> > how to do this)
> >
> > Any help is appreciated.. Also is there a good reference for[/color]
> understanding[color=green]
> > 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[/color]
> Access[color=green]
> > 2002, and Microsoft Access Inside Out-2002. None of these really have[/color][/color]
any[color=blue][color=green]
> > 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.
> >
> >
> >
> >[/color]
>
>[/color]

**Jerry Boone** · Nov 12 '05, 06:31 PM

Re: Sql Query Question

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.Exec ute("Select IDate from Table1
INNER JOIN Table2 ON Table1.DID = Table2.ID Where Table2.ID = " & Me.IID &
"").Fields(0).V alue

The "CurrentProject .Connection.Exe cute" is a hot connection to your data and
allows you to execute sql syntax.
The ".Fields(0).Val ue" 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" <jcmugs@hotmail .com> wrote in message
news:vvgv926fe7 4v36@corp.super news.com...[color=blue]
> 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" <bouncer@localh ost.not> wrote in message
> news:mD_Jb.3172 8$nK2.6899@nwrd dc01.gnilink.ne t...[color=green]
> > Is there a relationship between Table 1 and Table 1? If so, follow up[/color]
> here.[color=green]
> >
> > First thing... don't use "Date" as a Field name -- it is an Access[/color]
> reserved[color=green]
> > word, and sooner or later, it's going to cause confusion that will be[/color][/color]
very[color=blue][color=green]
> > 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[/color]
> make[color=green]
> > it easy to build and you can show whichever field will be meaningful to[/color]
> the[color=green]
> > user, and both fields in the dropdown list.
> >
> > Larry Linson
> > Microsoft Access MVP
> >
> >
> > "JC Mugs" <jcmugs@hotmail .com> wrote in message
> > news:vvgsglq7rc o7b7@corp.super news.com...[color=darkred]
> > > Help needed for project-Access 2002(office xp)
> > >
> > > PROBLEM: Figuring out how to lookup a record and DDate field in[/color][/color][/color]
Table1 -[color=blue][color=green][color=darkred]
> > > Take that DDate-field data from record looked up and assign it to Date[/color]
> > field[color=darkred]
> > > 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[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > customer file (Table3, but not related to this problem), The form is[/color][/color][/color]
an[color=blue][color=green][color=darkred]
> > > invoice entry and the invoices are written sometimes days in advance..[/color][/color]
> So[color=green]
> > I[color=darkred]
> > > was going to write the date to a table and access it from a lookup on[/color][/color]
> each[color=green][color=darkred]
> > > form. (Also it will help me access other data in other tables by[/color][/color]
> learning[color=green][color=darkred]
> > > how to do this)
> > >
> > > Any help is appreciated.. Also is there a good reference for[/color]
> > understanding[color=darkred]
> > > 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[/color]
> > Access[color=darkred]
> > > 2002, and Microsoft Access Inside Out-2002. None of these really have[/color][/color]
> any[color=green][color=darkred]
> > > 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.
> > >
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

Sql Query Question

Sql Query Question

Comment

Comment

Comment