Form data to table

[JLS04]

I've developed a form (with help from this forum) in which a user enters a
number of pieces of information. I would like to put a "button" in my form
that the user can click when finished, to post the information to a table.
Can this be done? Also, can I make certain fields required?

Thanks!

 

[Albert D.Kallal]

If the form is bound, then to write the data, you can use

if me.dirty then
me.dirty = false
end if

Also, can I make certain fields required?

In the table design mode, just set the field setting to required....

 

[JLS04]

Hi Albert,

Thanks for the reply. I'm afraid I'm goning to show just how little I know
about Access, but I'm a little confused - particularly on the code.

On binding the form - how do I bind the form? Is this where I link the form
to the table where I want the data to end up?

On the code, should I include this in the command button? Should I include
this in the properties of the command box - in "on enter?"

I apologize for being so simple! I'm not very good with VB code.

Thanks for your petience and help!
John

 

[Albert D.Kallal]

Hi Albert,

Thanks for the reply. I'm afraid I'm goning to show just how little I
know
about Access, but I'm a little confused - particularly on the code.

On binding the form - how do I bind the form? Is this where I link the
form
to the table where I want the data to end up?

Yes, if you create a blank form, and then set the data source to a table,
then that is called a bound form. You can also use the wizard to do this.

On the code, should I include this in the command button? Should I
include
this in the properties of the command box - in "on enter?"

ms-access forms as rule automatic save the data. But, if as you ask you want
to have a button that also does this, then yes, just place a button on the
form. You would then add the code to the buttons on click event. However, as
mentioned, ms-access will send the data to the table if YOU do not anyway,
so having this save button is kind of redundant..and not really necessary.


A sequence of screen shoots on how to add a button is here:

http://www.members.shaw.ca/AlbertKallal/wordmerge/Details.htm
(start reading from "Adding the command button to your Form"

However, don't type in the code in the above example, but just type in

me.Refresh

The above code will send the data to the table (you can also use the
me.dirty code, as that also will do this).

 

[JLS04]

Hey Albert,

Thanks again! I think I'm getting it. I have one more question (then I'll
leave you alone, I swear). When I link my form to a table, and identify
which fields in the table correspond to the fields on the form in the control
source, what do I do if I have code already in the control source? For
instance in one text box, I am using a dlookup to find specific information
based on another drop down box response. In the control source, I have ->
=DLookUp("[Line]","[LOB - All Companies]","[LOB] = '" & [Combo2] & "'"). I
want to identify the field this belongs in the corresponding table and in all
my other fields in the form, I use the "control source." When I try to do
this where I already have data, I end up screwing up the syntax. What do I
do?

Thanks again,
John

 

[Albert D.Kallal]

Hey Albert,

Thanks again! I think I'm getting it. I have one more question (then
I'll
leave you alone, I swear). When I link my form to a table, and identify
which fields in the table correspond to the fields on the form in the
control
source, what do I do if I have code already in the control source?

Good, you are clear on the concept that controls on a screen *CAN* be bound
to the forms underling source,but in some cases, those controls might have a
expression.

For
instance in one text box, I am using a dlookup to find specific
information
based on another drop down box response. In the control source, I have ->
=DLookUp("[Line]","[LOB - All Companies]","[LOB] = '" & [Combo2] & "'").

Good, the above example is a control that is NOT bound to the underlying
data source of the form.

I
want to identify the field this belongs in the corresponding table and in
all
my other fields in the form, I use the "control source." When I try to do
this where I already have data, I end up screwing up the syntax. What do
I
do?

?
Hum, does the combo box value in combo 2 also grab vales from the table
called [LOB - All Companies] ?

If yes, then simply make the combo2 box a multi-column combo box (use the
wizard....lets assume that we make a combo2 box that returns 3 columns:

LOB CompanyName Line

The combo box will return LOB, but display CompanyName, and line.

So, you could put the following expression in that text box to display line

=([combo2].[column](1))

The column function starts at 0 for the first column. So, just make sure
your combo2 box displays the extra columns you need, and then place controls
that simply display those extra columns as above.

Remember, since you can ALWAYS lookup the value in the other table, there is
no need to store those extra values. So, I am not really sure what you mean
by "identify" the field this belong to? Your dlookup code does look ok, but
you might find the column idea more easy..

Another way is to base the form on a query, and you left join in the
additional tables with values. And, you can also even use a sub-form to do
this. I explain this idea here
http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

 

[JLS04]

OK that makes sense. I think I confused the issue with the descriptions in
my question. I want to store responses selected in the form to a new table
(called new business) so I need to point each combo box and text box to a
field the "new business" storage table. For most of the combo boxes, in the
control source on the data tab, I can select a the field in the table the
form is bound to (which is the storage table). In the text boxes where I
have an expression in the control source, how can I direct the selections
made to a specific field in the "storage table."
Does the question make sense?

Thanks yet again! Your assistance has really helped me develop this database!

Hey Albert,

Thanks again! I think I'm getting it. I have one more question (then
I'll
leave you alone, I swear). When I link my form to a table, and identify
which fields in the table correspond to the fields on the form in the
control
source, what do I do if I have code already in the control source?

Good, you are clear on the concept that controls on a screen *CAN* be bound
to the forms underling source,but in some cases, those controls might have a
expression.

For
instance in one text box, I am using a dlookup to find specific
information
based on another drop down box response. In the control source, I have ->
=DLookUp("[Line]","[LOB - All Companies]","[LOB] = '" & [Combo2] & "'").

Good, the above example is a control that is NOT bound to the underlying
data source of the form.

I
want to identify the field this belongs in the corresponding table and in
all
my other fields in the form, I use the "control source." When I try to do
this where I already have data, I end up screwing up the syntax. What do
I
do?

?
Hum, does the combo box value in combo 2 also grab vales from the table
called [LOB - All Companies] ?

If yes, then simply make the combo2 box a multi-column combo box (use the
wizard....lets assume that we make a combo2 box that returns 3 columns:

LOB CompanyName Line

The combo box will return LOB, but display CompanyName, and line.

So, you could put the following expression in that text box to display line

=([combo2].[column](1))

The column function starts at 0 for the first column. So, just make sure
your combo2 box displays the extra columns you need, and then place controls
that simply display those extra columns as above.

Remember, since you can ALWAYS lookup the value in the other table, there is
no need to store those extra values. So, I am not really sure what you mean
by "identify" the field this belong to? Your dlookup code does look ok, but
you might find the column idea more easy..

Another way is to base the form on a query, and you left join in the
additional tables with values. And, you can also even use a sub-form to do
this. I explain this idea here
http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

In the text boxes where I

have an expression in the control source, how can I direct the selections
made to a specific field in the "storage table."
Does the question make sense?

Hum, the problem here is since it is a expression, the next time you bring
up the same record, you should get the same expression, and thus there is no
need to store this stuff...right?

Perhaps I missed something here. If you are talking about saving the data
into a different table, then likely a sub-form is what you need. However, if
you got a expression that works the way you want, then why bother saving
it...as the next time you return to that form, the expression should still
work? I guess I am trying to point out that there is little, or no need to
store those expressions, since they will still be the same when you return
next time. If there is some field value in the expression that changes, then
that needs to be saved, but not the actual expression.

You can correct me on the above, but why go to a bunch of work to save a
expression that will still work next time you return. And, even better, but
not saving the expression, you can ALWAYS be sure it is the correct value,
since you did NOT SAVE it. If you save the expression, then what happens
later on if you run some code that updates other values that the expression
is based on? You now will have to write a bunch more code to somehow update
those expressions. So, best just leave it as is, and the values will never
be out of sync...

 

[JLS04]

I'm probably doing a lousy job explaining this and making this more confusing
than it needs to be. As you suggested, I am trying to save the data into
another table. A user will enter data in the form - customer name (manually
typed), units sold (manually typed), item (drop down selection), salesperson
(drop down selection), etc. These entries in the form will create "answers"
in other text boxes. For instance, based on the item and the number of units
sold, a selling price will be plugged into a text box automatically (using a
dlookup based on the item sold) and a total value of the sale will be
calcluated in another (selling price X units sold). What I am hoping to do
is, when the user is done entering data, append all these answers about a
specific sale into a separate table for future reporting purposes. Reports
like, who sold how much of what, etc. I may not be using the form correclty,
but it seems to be close to working how I envisioned. Thanks, John.

 

[Albert D.Kallal]

Ah, ok, then we likely are taking about a classic invoice, or orders type
screen.

The solution here is to use a sub-form. And, to allow the user to enter many
values, then likely a sub-form in continues mode is the solution.

Take a look at the following screen shots, and note a few that are similar
to what you need.

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

So, likely we would have a few columns in this sub-form.

The first combo could be the customer name. However, if you have to enter a
BUNCH for a given customer, or they belong as a group to a particular
customer order, then likely you need a main form where you bring up the
customer, and then a sub-form where you enter the things sold, price etc
into the sub-form. (two forms = two tables. So, the sub-form is the means by
which you send data to another related table).

If the data entry is not fixed around first finding a particular customer,
but you just need to enter the data, then you still need a continues form,
but likely not a master + child form to group things by a particular
customer.

Regardless of how you need to group this data entry problem, the details
data should be in a continues form.

You would have

Customer units Sold item Unit
Price Salesperson
abc 10 Apples
Joe
def 5 Water melons
Albert
etc. etc.

So, to enter the data as a grid, the first column would be a combo box
(search by customer name..but stores the cust id).

Now, for the item combo, that combo can also be 3 columns

id Descripton unitPrice
3 Apples .3
55 Water mellons .4

So, you can in the after update event of the item comb box place the value
of the unit print into the unit Price.

me!UnitPrice = me.itemCombo.column(2)

The end result of this whole process is that you don't need to write code to
send the data to that other table, but in fact simply has a sub-form that
lets you ENTER the data....

This approach is a LOT less work.

You should probably take a look at the northwind sample that ships with
ms-access.

(you can find this sample under help->sample databases at least that were
it is in a2003).