Help Please!

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hey, my names Paul and I am stuck with the following
situation. Any help would be greatly appreciated.

A week ago I wrote the following:
----------------
OK. I need a macro, I think, when I click on it on a form
from a button I want it to run a query and transfer data
from one field of the query to my form. How is this
possible.
----------------

Then I got the following reply which was useful:

----------------
Hi,

I am assuming you want the data returned based on
something entered into the form. Try the following:

Open form in design mode. Click properties of field that
is being filled in on form. Click on lost focus.

Fill the following in:

Forms![field_to_fill_in]=DLookup
("[returned_field_from_table ]", "Table_name", "[Table_fie
l
d_to_match] =" & Forms![Form_name]!form_entered_field)

field_to_fill_in <-- field on your form that you are
populating from the search.
returned_field_from_table <-- the data just retrieved
Table_name <-- table being searched
Table_field_to_match <-- field in table that links to
field_to_fill_in
Form_entered_field <-- field on form that was typed into

Hope this helps you.
----------------

I am, however getting an error message. I used this
command which I typed up in the VB editor

----------------
Forms![heat/stageID]=DLookup("[Heat/StageID]", "
Heat/Stage Table", "[ Heat/StageID] =" & Forms![Results
Table]! Competitor1)
----------------

I am using this command triggering it with a command
button. The button is on the form 'Results Table'. I want
the command to go to the 'heat/stage table', look up a
record according to the 'heat/stageID' on the
form 'Results Table', find the ID in the 'heat/stageID'
on the table 'heat/stage table' and find the entrant 1
name, then copy it over to the form 'Results Table'.

Any help will be greatly appreciated.

Thanks Paul
 
Paul,

There were a couple of errors in the expression which was earlier
suggested to you. And in any case, there are a few questions still
about what you are doing.

Assuming you simply want to display the entrant 1 name corresponding
with the current Heat/StageID, and you want to use a DLookup function
as suggested, you should use an unbound textbox on your form, and in
its control source property, the equivalent of...
=DLookup("[Entrant 1 Name]","Heat/Stage Table","[Heat/StageID]=" &
[Heat/StageID]
(this assumes that Heat/StageID is a number!)

By the way, as an aside, it is not a good idea to use a / as part of
the name of a field or database object.

However, this is not the easiest way to do it. It would be easier to
have your form based on a query which includes the Heat/Stage Table,
suitably joined on the Heat/StageID field, and then you have direct
access to the Competitor's name to include on the form.

However, I am suspicious that you are actually trying to effect a copy
of the competitor's name form one table to another. Am I right? If
so, this is another question, but in essence this would be a violation
of correct database design principles, and in the end just making it
harder for yourself.

I am also suspicious, just reading between the lines, that you have
the competitors listed for, each Heat/Stage, in separate fields, i.e
Entrant1, Entrant2, etc... am I right? If so, once again, the design
should be reviewed - this data should be in individual records in a
separate related table.

- Steve Schapel, Microsoft Access MVP


Hey, my names Paul and I am stuck with the following
situation. Any help would be greatly appreciated.

A week ago I wrote the following:
----------------
OK. I need a macro, I think, when I click on it on a form
from a button I want it to run a query and transfer data
from one field of the query to my form. How is this
possible.
----------------

Then I got the following reply which was useful:

----------------
Hi,

I am assuming you want the data returned based on
something entered into the form. Try the following:

Open form in design mode. Click properties of field that
is being filled in on form. Click on lost focus.

Fill the following in:

Forms![field_to_fill_in]=DLookup
("[returned_field_from_table ]", "Table_name", "[Table_fie
l
d_to_match] =" & Forms![Form_name]!form_entered_field)

field_to_fill_in <-- field on your form that you are
populating from the search.
returned_field_from_table <-- the data just retrieved
Table_name <-- table being searched
Table_field_to_match <-- field in table that links to
field_to_fill_in
Form_entered_field <-- field on form that was typed into

Hope this helps you.
----------------

I am, however getting an error message. I used this
command which I typed up in the VB editor

----------------
Forms![heat/stageID]=DLookup("[Heat/StageID]", "
Heat/Stage Table", "[ Heat/StageID] =" & Forms![Results
Table]! Competitor1)
----------------

I am using this command triggering it with a command
button. The button is on the form 'Results Table'. I want
the command to go to the 'heat/stage table', look up a
record according to the 'heat/stageID' on the
form 'Results Table', find the ID in the 'heat/stageID'
on the table 'heat/stage table' and find the entrant 1
name, then copy it over to the form 'Results Table'.

Any help will be greatly appreciated.

Thanks Paul
Click to expand...
 
Back
Top