DLOOKUP DIDN'T work

  • Thread starter Thread starter OBWAN
  • Start date Start date
O

OBWAN

How could I show field data from a child table where
childtable.autoID equals maintable.autoID on the main form?

In other words, if the user is on the main form, but wants
a textbox on the mainform with data from a child table
that is usually accessed from a subform. The
controlsource is not in the underlying table.


DLOOKUP did not work, though I got no erros when I tried
it.
 
OBWAN said:
How could I show field data from a child table where
childtable.autoID equals maintable.autoID on the main form?

In other words, if the user is on the main form, but wants
a textbox on the mainform with data from a child table
that is usually accessed from a subform. The
controlsource is not in the underlying table.


DLOOKUP did not work, though I got no erros when I tried
it.
Click to expand...


DLookup should work, what did you use?

Assuming that autoID is a numeric type field, a text box
with the expression should wok:
=DLookup("field", "childtable", "autoID=" & Me.txtxautoID)

Where you have to replace field with the name of the field
you want to look up in the childtable amd txtxautoID with
the name of the main form text box that is bound to the
maintable field autoID.
 
I must be doing something wrong

I get an error:

THE OBJECT DOESN'T CONTAIN THE AUTOMATION OBJECT 'Me.'

What's happening Functionally:

If I bring up the subform and select data and then check
the table, the selection is placed into the table, no
problem.

If I click on the Mainform_combobox no selection is made,
the combobox doesnot fold-up, it just hangs open.

If I then try to click on commandbutton_open_subform
I get run time error 2447 - invalid use of the .(dot) or !
operator or invalid parenthesis, and I never got that
error until I used Dlookup as the control source.

Private sub Mainform_combobox_lostfocus()
CurrentDb.Execute "update childtable Set childfield ='"
& Me.mainform_combobox & "'Where ID=" & Me.ID

End sub
 
I feel completely lost here. I'm getting the idea that you
have a lot more going on than I'm aware of. I've never
heard of a combo box that drops down and then just sits
there.

How did we get from a DLookup question to one about
executing an UPdate query???

The use of a command button to "open" a subform is a foreign
concept to me and I have no idea what you're talking about
with that???

I think you need to step through the code a line at a time
to make sure that each step works as expected? Does the
DLookup work by itself if you use it in your code or the
immediate/debug window?
 
First, I appreciate you sticking with me on this...thanks.

I've never heard of a combo box that drops down and then
Click to expand...
just sits there.

Please understand I don't want this result, I don't want
it to just sit there.

The use of a command button to "open" a subform is a
Click to expand...
foreign concept to me and I have no idea what you're
talking about with that???

I have a command button whose purpose is to make a subform
..visible property = true , thus, in effective to the
user, "opens" the subform.............sorry for my
vernacular, it is quite user/laymen.

Another "command button" "HIDE" sets .visible = false

Does the DLookup work by itself if you use it in your
Click to expand...
code or the >immediate/debug window?

The Dlookup is in the control source property line of the
properties "screen" in mainform_combobox


maybe we could focus on why and when this message occurs.
 
OBWAN said:
First, I appreciate you sticking with me on this...thanks.


just sits there.

Please understand I don't want this result, I don't want
it to just sit there.


foreign concept to me and I have no idea what you're
talking about with that???

I have a command button whose purpose is to make a subform
.visible property = true , thus, in effective to the
user, "opens" the subform.............sorry for my
vernacular, it is quite user/laymen.

Another "command button" "HIDE" sets .visible = false


code or the >immediate/debug window?

The Dlookup is in the control source property line of the
properties "screen" in mainform_combobox
Click to expand...

Ok, I think I'm getting the picture now.

All this confusion over my mistake, sorry but I was thinking
the DLookup was in a VBA procedure even while I made it a
control source expression, aaarrgghhh!

Control source expressions do not understand Me.
Try this instead:

=DLookup("field", "childtable", "autoID=" & txtxautoID)
--
Marsh
MVP [MS Access]


 
Ok, I think I'm getting the picture now.
All this confusion over my mistake, sorry but I was
thinking the DLookup was in a VBA procedure even while I
Click to expand...
made it a control source expression, aaarrgghhh!
=DLookup("field", "childtable", "autoID=" & txtxautoID)
Click to expand...


Thanks.....the combobox on the MAINFORM is now displaying
the correct value from the Table2..........and I can
probably live with that..........

But there are still problems if you will indulge me.

The MAINFORM combobox still drops down and when I use the
cursor to select a value from
ROW SOURCE: "cash", "credit", "etc"
ROW SOURCE TYPE: value list
the combobox does not close up with the selected value.
I guess maybe that is a good thing and maybe I should
change the combobox into a textbox, because if I was able
to select a new value (let's say "credit"), I don't see
how I could change the corresponding record in Table2
to "credit" ------ and then Table2 would really be holding
the value "cash" while the mainform text box is
displaying "credit". If I change the combobox to a
textbox display only, I could have a commandbutton next to
it that makes the subform for Table2 visible and the user
could change the Table2 value using the Table2 Listbox.

If there is a way to have the combobox select a value from
it's ROW SOURCE and update Table2 (maybe using VBA code?)
then please tell me so I can learn more about DLOOKUP.

Also, in your syntax "autoid=" & txtxautoID
which one represents MAINFORM and which one represents
Table2?
 
OBWAN said:
made it a control source expression, aaarrgghhh!



Thanks.....the combobox on the MAINFORM is now displaying
the correct value from the Table2..........and I can
probably live with that..........

But there are still problems if you will indulge me.

The MAINFORM combobox still drops down and when I use the
cursor to select a value from
ROW SOURCE: "cash", "credit", "etc"
ROW SOURCE TYPE: value list
the combobox does not close up with the selected value.
I guess maybe that is a good thing and maybe I should
change the combobox into a textbox, because if I was able
to select a new value (let's say "credit"), I don't see
how I could change the corresponding record in Table2
to "credit" ------ and then Table2 would really be holding
the value "cash" while the mainform text box is
displaying "credit". If I change the combobox to a
textbox display only, I could have a commandbutton next to
it that makes the subform for Table2 visible and the user
could change the Table2 value using the Table2 Listbox.

If there is a way to have the combobox select a value from
it's ROW SOURCE and update Table2 (maybe using VBA code?)
then please tell me so I can learn more about DLOOKUP.

Also, in your syntax "autoid=" & txtxautoID
which one represents MAINFORM and which one represents
Table2?
Click to expand...


autoID is in the table specified in the DLookup's second
argument while txtxautoID is the name of the text box on the
main form.

I think we have another misunderstanding going on here.
DLookup just retrieves a field's value from childtable
(table2?), it does not do anything about updating a field in
any table.

I guess my picture of what you're trying to do is still not
very clear. You say above that you have the DLookup in the
combo box's control source? Sorry, but that really doesn't
make sense, could it be that this whole DLookup discussion
has just been a diversion from the real issue? Let's try
starting over from scratch.

The way I'm now reading what you say above, you want to
select a value from the combo box's value list, but instead
of it being saved in the parent table you want to place it
in the corresponding record in the child table. If that's
correct, then DLookup doesn't come into it at all. Leave
the combo box's control source blank. You can then use the
combo box's AfterUpdate event to Execute the Update query
you had in an earlier post to save the combo's value in
child table.

How do you deal with the situation where the record in the
child table has not been created yet (i.e. there is no
record to update)?
 
Back
Top