Problems with a macro driven data entry form

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I have a data entry form ( reciepts) linked to a table( Donations) that
accepts data into temporary fields ( unbounded text boxes)
An "enter" button on the form kicks off a macro that
1 adds a record to the underlying table Donations
2 uses a series of SetValue statements to transfer the values from the
temporary fields on the form to the appropriated fields in the table
Donations.

Yes, yes, it would be a lot easier to do a direct data entry form but I get
a lot of wasted null records appended to the table every time the user
clicks this form and then closes.

Basicallyy I need to control the data entry process before the data hits the
table.
Does that make sense ?

Richard ( non VB but loves macros)
 
Forgot to mention the method below does not work, I get a macro halted
message :

The object Donations is closed.

Which I cannot figure thinking that as the Donations table is the data
source for the initial form it shoul dbe open?
 
Richard,

No, the table is not open unless the table is open. Your form provides
an avenue through which you can see the data in the table, but it does
not open the table. In any case, the SetValue macro is not an
appropriate way to mainipulate data in a table. You would be much
better trying with an Append Query or an Update Query, and then, if you
need to manage this from your macro, the OpenQuery action to run the
Update or Append.

If the only reason for these convoluted gymnastics is to solve the
problem of "wasted null records", then I think there would be a lot
simpler ways of handling that problem. First of all, you need to figure
out how/why these "null records" are getting created, as this in itself
is quite irregular, and does not happen simply on the basis of a form
getting opened and closed. Then, there are a number of ways of checking
data before a record is created. For example, fields can have the
Required property set to Yes in the table design, or you can use a
field's Validation Rule property, to make sure a record is not created
without valid data in these fields. Or you can use the Before Update
event of the form to ask the user to confirm the creation of a new
record before the form is closed.
 
Thanks for your thoughts Steve, you are right in that I need to step back a
little here an take a closer look at my user interface and the data
validation really needed in my case.

Richard
 
Back
Top