Enter values in table from form using dlookup

[Guest]

Hi,
Just wondering if it's possible to put values in a table, using data entered
on a form?
Like if the person enters for item a, 1, then it adds 1 to item A in a table.
I've tried dlookup(blah) = me.text1 but it doesn't work.
Thanks

 

[Fred Boer]

Dear s4:

What you want is the way it is supposed to work. You don't need to do a
lookup to move data from a form to a table, you simply need to "bind" your
form to the table (or a query based on the table). Every form has a "Record
Source" property; which should be the name of the table or query you want to
be the source of data on the form. Specifying your table in this property
"binds" the form to your table. You can then create controls (textboxes,
etc.), that display data from the table. Editing data in these controls
modifies the data in the table. No need for lookups!

If you are just starting out, you may want to try using the Wizard to build
some forms for you to see how this works - the Wizard works well in creating
basic forms.

HTH

 

[Wolfgang Kais]

Hallo "s4".

Hi,
Just wondering if it's possible to put values in a table, using
data entered on a form?
Like if the person enters for item a, 1, then it adds 1 to item
A in a table. I've tried dlookup(blah) = me.text1 but it doesn't
work.

The data displayed in a form can come from a table or query, you can
specify the data source through the RecordSource property of the
form. You can use the wizard to create such a form, or you can create
it manually. After setting the RecordSource property, you have
several opportunities to add a data bound control to your form:
1) (easiest)
Drag a field from the field list (see the view menu) onto the form.
2) (most complicated)
Click a control type in the toolbox, then click a position on the
form. For some control types there is a wizard that can help you
to specify important properties like the field from the data source
that should be bound to the control. If there's no wizard available
(like with textboxes), you have to specify the ControlSource of the
control using the properties window.
3) (somewhere in between)
Click a tool in the toolbox, then drag a field from the field list.
This creates a control of your choice, bound to the field.

You code using dlookup can't work because DLookup returns a value
(not a field) from a table or query. Using this function means:
"Tell me the (first) value of an expression for that table/query
where certain criteria are met".

To change a value in a table via code you can either "execute" an
update query, or you can open a "recordset", locate the desired
record and edit/update the desired field ("blah"?).
Search online help for the words in quotes for details.

 

[Guest]

Thanks,
I've actually got a different form linked to the table, I was wanting to use
this form to enter amounts to ADD to the amounts already in the table. It's
basically a quantity thing.
Thanks

 

[David Portwood]

Two fields: OriginalAmount (bound) and AddedAmount (unbound). In the form's
BeforeUpdate event, add AddedAmount to OriginalAmount and put null in
AddedAmount.

 

[Fred Boer]

Dear s4:

Depending on your needs, you could do this simply by having the form bound
to the table, and providing an unbound control (textbox, for example). The
user would enter the value to be added to the existing amount into this
unbound textbox. Then you could use a bit of code in the AfterUpdate event
to automatically add the amount to the table value, or you could create a
command button which would make the process one which requires the user to
click on a button.

Post back if this doesn't solve your problem!

Cheers!

 

[David Portwood]

Pardon me if my terminology is confusing: when I say "fields" I'm referring
to controls on a form which are either bound or unbound to corresponding
fields in the underlying table/query.

So why can't I just say "controls"? Well, my background is from another
database environment which didn't distinguish between fields on a form and
fields in the underlying table. When I first read about "controls" in an
Access textbook I thought it was very odd terminology. I'm still adjusting.

 

[Bob Quintal]

Pardon me if my terminology is confusing: when I say "fields" I'm
referring to controls on a form which are either bound or unbound
to corresponding fields in the underlying table/query.

So why can't I just say "controls"? Well, my background is from
another database environment which didn't distinguish between
fields on a form and fields in the underlying table. When I first
read about "controls" in an Access textbook I thought it was very
odd terminology. I'm still adjusting.

Well those things on the form "control" what and how the data gets
into / out of the tables.

Q

 

[pietlinden]

Thanks,
I've actually got a different form linked to the table, I was wanting to use
this form to enter amounts to ADD to the amounts already in the table. It's
basically a quantity thing.
Thanks

why would you want to do it this way? BAD design. Use a query to
determine quantity on hand. Read Allen Browne's articel on his
website. www.allenbrowne.com it's in the solutions, Quantity on
Hand ..

 

[David Portwood]

So why can't I just say "controls"? Well, my background is from

Well those things on the form "control" what and how the data gets
into / out of the tables.

Yes, and I am getting used to that terminology. I've decided that no matter
what term Access uses, it is certainly a good thing to distinguish "fields"
on a form versus fields in the underlying table.