Get information to a form from another table

[Guest]

Tina,

It's on the way. I'm requesting a read-reciept just to make sure I got the
e-mail correct.

Thanks,
Johnie Karr

well, there's no reason i can think of that it wouldn't work as outlined. if
you're using Access97 or newer, i'll take a look at your db and
troubleshoot, if you want to email it to me. to do that: copy the db and
delete any proprietary data from the copy, enter dummy records if necessary
so i'll have something to work with. compact the copy, and zip it if you can
(i have dialup, so slow downloading). refer to this newsgorup in the email
subject line, so it doesn't get deleted as spam, and refer to this thread in
the message text so i can find it to post back. my email address is at
http://home.att.net/~california.db/tips.html#aTip11, just follow the
instructions in the Example and also change the 2 to a 1.

hth

Tina,

No, it isn't working. I've updated my control source with your code
(currect syntax) and have this in my VBA:

Private Sub Form_Current()
Me!Invoiced.Requery
End Sub

Me!Invoiced is the name of my checkbox. I know in one post I said it was
isInvoiced, but I changed it.

This is the code that gives me the error I displayed earlier.

Any thoughts?

Thanks,
Johnie Karr

okay. so is it working for you, or not working?


Tina,

The method demonstrated in the pdf file you told me about is how I created
the event procedure for the on current.

Thanks,
Johnie Karr

:

comments inline.

I'm using the code that Douglas Steel posted in my checkbox's
ControlSource.

i told you how to check to data type of JobNumber, and you posted back
that
you verified it as Text data type. Doug's post asked you if the field is
Number data type, and gave you code to fit that data type. if it IS
number
data type, that's fine - but you need to check your table and see what
the
field's data type actually is, once and for all, and use the correct
syntax
for that data type.


Then I use the requery line that Tina provided in my form's on_current
and
I
get this error:

The Expression On Current You Entered as the event property setting
produced
the following error: Object or class does not support the set of
events.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [event procedure].
*There may have been an error evaluating the function, event, or
macro.

looks like you've put the code in the Current event "line" in the
Properties
box in the form's design view. you have to put the code in the Current
event
*procedure*, as i said before. if you don't know how to create an event
procedure in a form, go to
http://home.att.net/~california.db/instructions.html and click the
CreateEventProcedure link, then follow the illustrated instructions.

hth


Thanks,
Johnie Karr

:

you have a checkbox control on the form. set the control's
ControlSource
property to

=DLookUp("[Invoiced]","[Invoice]","[JobNumber]='" & [Forms]![Order
Entry]![Order Number] & "'")

the above goes all on one line, of course.

in the form's Current event procedure, add a line of code to requery
the
checkbox control, as

Me!CheckboxControlName.Requery

change CheckboxControlName to the correct name of the checkbox
control,
of
course.

hth


message
I did an expression for the forms on current event:

Me!isInvoiced=DLookUp("[Invoiced]","[Invoice]","[JobNumber]='" &
Forms![Order Entry]![Order Number] & "'")

and all I get is an error. Of course it's all one line in the
code
thing.
I also tried this in the vba code section, but still just keep
getting
an
error.

Thanks,

:

okay. in that case, the syntax you originally posted for the
DLookUp()
function was correct, as

=DLookUp("[Invoiced]","[Invoice]","[JobNumber]='" &
[Forms]![Order
Entry]![Order Number] & "'")

....and, well, duh, sometimes i get to things the long way
around.
since
this
is a DLookUp() function, it will run when the form opens - and
not
again,
unless you requery the checkbox control to force it to run. so
you'll
need
to add code to the form's Current event to requery the checkbox
control.

hth


message
Order Number and JobNumber are both text.

The Order Number in the Order Entry table is the reference for
the
JobNumber
in the Invoice Table.

The Invoiced checkbox in the Invoice table is Yes/No.

Thanks,
Johnie Karr

:

reading back over the thread, perhaps we've been talking at
cross-purposes.
the field that is bound to the Invoiced checkbox control, on
the
first
form,
has a Yes/No data type, correct? what are the data types of
the
Order
Number
and JobNumber fields, respectively? to see what a field's
data
type
is,
you
have to go to the *table* the contains the field, open the
table
in
design
view, and look at the field's data type there.

hth


in
message
Tina,

Thank you for the suggestion, but it still does not work.

All I'm showing is a greyed out checkbox.

I placed your code in the control source for the checkbox
named
Invoiced
on
the Order Entry form.

Also, it does not need to save it to the table as long as
it
can
pull
it's
information from the invoice table because the order entry
dept
just
needs
to
know if it's been invoiced if a customer calls.

Thanks,
Johnie Karr

:

the JobNumber field and the Order Number field are
*checkboxes*?
that
implies that the fields are Yes/No data type. is that
correct?
if
so,
then
your syntax is slightly off, since it treats JobNumber
as a
string
(Text
data type) value. try

=DLookUp("[Invoiced]","[Invoice]","[JobNumber]=" &
[Forms]![Order
Entry]![Order Number])

also note that placing the above expression in a
control's
ControlSource
property will allow the control to *display* the value -
but
it

 

[tina]

okay, first of all, the Invoiced field in table Invoice is a Lookup field.
get rid of the Lookup (change the the DisplayControl property in the table
design to Check Box). if you want to use a combo box control bound to that
field in a form, then create the control *at the form level*, don't set it
up at the table level. recommend you remove all Lookup fields that you may
have in all your tables. for more information, see
http://home.att.net/~california.db/tips.html#aTip8.

next, you need to change the name of the checkbox control in the OrderEntry
form, since it is causing a circular reference error. i changed it from
Invoiced to chkInvoiced.

next, change the expression in the checkbox control's ControlSource
property, as

=Nz(DLookUp("[Invoiced]","[Invoice]","[JobNumber]='" & Forms![Order
Entry]![Order Number] & "'"),False)

for some reason, the DLookup() function doesn't return False when the value
in the table is False - it returns Null, so you're getting either a check,
or a "gray" box. using the Nz() function in the expression will force the
expression to return either True or False - checkmark, or "white" box. also
recommend that you change the Tabstop checkbox control's TabStop property
setting from Yes to No. usually when a control is not editable, it's less
confusing to the user if they can't automatically tab into it.

last, in the code on the form's Current event, change the code to refer to
the newly renamed checkbox control, as

Me!chkInvoiced.Requery

this takes care of the problem you posted about. here are a few other
suggestions for your database:

1) turn OFF the NameAutocorrect option in your db. for more information, see
http://home.att.net/~california.db/tips.html#aTip3.

2) remove the subdatasheet links in your tables. for more information, see
http://home.att.net/~california.db/tips.html#aTip6 and, while you're there,
take a look at http://home.att.net/~california.db/tips.html#aTip5 as well.

3) enforce referential integrity in the relationship between the Invoice and
Order Entry tables.

4) require variable declaration in your VBA modules. in existing modules, go
to the top of the VBA Editor screen and type in Option Explicit directly
below Option Compare Database, as

Option Compare Database
Option Explicit

to set up the VBA Editor so the line is added to all new modules
automatically, open the VBA Editor window and on the menu bar click Tools |
Options | Editor tab and checkmark the box next to Require Variable
Declaration. this setting will apply to all applications you build or open
in that copy of Access on that computer. also make sure that you compile the
code after every change, as you continue to program your database.

last - and most importantly - i strongly recommend that you read up on
normalization principles. of the two tables i looked at, one has nearly 100
fields and the other has upward of 200 fields. in a properly normalized
table, there are rarely more than 25 or 30 fields, tops. this looks like a
mission-critical database for your business, so even though you've built
quite a lot of it, i really urge you to learn the basic principles of
relational design and consider restructuring the database; in the long run,
you won't be sorry you did. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

Tina,

It's on the way. I'm requesting a read-reciept just to make sure I got the
e-mail correct.

Thanks,
Johnie Karr

well, there's no reason i can think of that it wouldn't work as outlined. if
you're using Access97 or newer, i'll take a look at your db and
troubleshoot, if you want to email it to me. to do that: copy the db and
delete any proprietary data from the copy, enter dummy records if necessary
so i'll have something to work with. compact the copy, and zip it if you can
(i have dialup, so slow downloading). refer to this newsgorup in the email
subject line, so it doesn't get deleted as spam, and refer to this thread in
the message text so i can find it to post back. my email address is at
http://home.att.net/~california.db/tips.html#aTip11, just follow the
instructions in the Example and also change the 2 to a 1.

hth

Tina,

No, it isn't working. I've updated my control source with your code
(currect syntax) and have this in my VBA:

Private Sub Form_Current()
Me!Invoiced.Requery
End Sub

Me!Invoiced is the name of my checkbox. I know in one post I said it was
isInvoiced, but I changed it.

This is the code that gives me the error I displayed earlier.

Any thoughts?

Thanks,
Johnie Karr

:

okay. so is it working for you, or not working?


Tina,

The method demonstrated in the pdf file you told me about is how I created
the event procedure for the on current.

Thanks,
Johnie Karr

:

comments inline.

I'm using the code that Douglas Steel posted in my checkbox's
ControlSource.

i told you how to check to data type of JobNumber, and you

posted
back

that
you verified it as Text data type. Doug's post asked you if the field is
Number data type, and gave you code to fit that data type. if it IS
number
data type, that's fine - but you need to check your table and

see
what

the
field's data type actually is, once and for all, and use the correct
syntax
for that data type.


Then I use the requery line that Tina provided in my form's on_current
and
I
get this error:

The Expression On Current You Entered as the event property setting
produced
the following error: Object or class does not support the set of
events.

*The expression may not result in the name of a macro, the

name of
a

user-defined function, or [event procedure].
*There may have been an error evaluating the function, event, or
macro.

looks like you've put the code in the Current event "line" in the
Properties
box in the form's design view. you have to put the code in the Current
event
*procedure*, as i said before. if you don't know how to create

an
event

procedure in a form, go to
http://home.att.net/~california.db/instructions.html and click the
CreateEventProcedure link, then follow the illustrated instructions.

hth


Thanks,
Johnie Karr

:

you have a checkbox control on the form. set the control's
ControlSource
property to

=DLookUp("[Invoiced]","[Invoice]","[JobNumber]='" & [Forms]![Order
Entry]![Order Number] & "'")

the above goes all on one line, of course.

in the form's Current event procedure, add a line of code to requery
the
checkbox control, as

Me!CheckboxControlName.Requery

change CheckboxControlName to the correct name of the checkbox
control,
of
course.

hth


message
I did an expression for the forms on current event:

Me!isInvoiced=DLookUp("[Invoiced]","[Invoice]","[JobNumber]='"
&

Forms![Order Entry]![Order Number] & "'")

and all I get is an error. Of course it's all one line in the
code
thing.
I also tried this in the vba code section, but still just keep
getting
an
error.

Thanks,

:

okay. in that case, the syntax you originally posted for the
DLookUp()
function was correct, as

=DLookUp("[Invoiced]","[Invoice]","[JobNumber]='" &
[Forms]![Order
Entry]![Order Number] & "'")

....and, well, duh, sometimes i get to things the long way
around.
since
this
is a DLookUp() function, it will run when the form

opens -
and

not
again,
unless you requery the checkbox control to force it to

run.
so

you'll
need
to add code to the form's Current event to requery the checkbox
control.

hth


"Johnie Karr" <[email protected]>

wrote
in

message
Order Number and JobNumber are both text.

The Order Number in the Order Entry table is the

reference
for

the
JobNumber
in the Invoice Table.

The Invoiced checkbox in the Invoice table is Yes/No.

Thanks,
Johnie Karr

:

reading back over the thread, perhaps we've been

talking
at

cross-purposes.
the field that is bound to the Invoiced checkbox control, on
the
first
form,
has a Yes/No data type, correct? what are the data

types
of

the
Order
Number
and JobNumber fields, respectively? to see what a field's
data
type
is,
you
have to go to the *table* the contains the field,

open
the

table
in
design
view, and look at the field's data type there.

hth



Tina,

Thank you for the suggestion, but it still does

not
work.

All I'm showing is a greyed out checkbox.

I placed your code in the control source for the checkbox
named
Invoiced
on
the Order Entry form.

Also, it does not need to save it to the table as

long
as

it
can
pull
it's
information from the invoice table because the

order
entry

dept
just
needs
to
know if it's been invoiced if a customer calls.

Thanks,
Johnie Karr

:

the JobNumber field and the Order Number field are
*checkboxes*?
that
implies that the fields are Yes/No data type. is that
correct?
if
so,
then
your syntax is slightly off, since it treats JobNumber
as a
string
(Text
data type) value. try

=DLookUp("[Invoiced]","[Invoice]","[JobNumber]=" &
[Forms]![Order
Entry]![Order Number])

also note that placing the above expression in a
control's
ControlSource
property will allow the control to *display* the value -
but
it