Required/Optional Fields

  • Thread starter Thread starter Michael Conroy
  • Start date Start date
M

Michael Conroy

I have an unbound form for entering a new author into my library database.
The firstname and lastname fields are required, but the web address is
optional. If I use an append query, I have to check if the URL field is null
(if statement) and use separate SQL strings accordingly. I haven't tried a
DAO recordset yet, but I am assuming the same problem. One program
requirement, I want to give the user the option of not saving, so I don't
want to bind the form to the table and get lots of empty autonumber fields.
So, what is the trick? What if the form had five required fields and five
optional fields, how do you program VBA for this contigency? As always, any
help would be greatly appreciated.
 
What difference does it make if there are unused autonumbers? 1, 2, 5, 6, 8
serves the purpose just as well as 1, 2, 3, 4, 5.

Put code in the form's BeforeUpdate event to check that the required fields
have values. Set Cancel = True if one or more fields is incomplete.
 
Doug, thanks for responding. Maybe I am over complicating the new record
process. I thought using unbound forms was preferred so the data could be
checked beforehand. Also looking for duplicates or making sure a date is a
real date, etc. I guess I could do this in the beforeupdate event. Regarding
the autonumber, I suppose I was just trying to have a clean table with no
empty records. I guess after a few years of coding I thought binding a form
to a table seemed like a rookie maneuver and that programming the event was
better.
--
Michael Conroy
Stamford, CT


Douglas J. Steele said:
What difference does it make if there are unused autonumbers? 1, 2, 5, 6, 8
serves the purpose just as well as 1, 2, 3, 4, 5.

Put code in the form's BeforeUpdate event to check that the required fields
have values. Set Cancel = True if one or more fields is incomplete.
Click to expand...
 
Realistically, I don't believe unbound forms are preferred for anything.
They're a lot more work, and you lose some of the advantage of using Access
as a RAD tool.

Using the form's BeforeUpdate to check for completeness is a pretty standard
method.

If you're concerned about gaps in the numbering, don't use an AutoNumber
field: "roll your own" instead. Again, you'd put code in the BeforeUpdate
event so that once the data has passed all edits, you'd determine what Id
value to assign the new record by determining what largest number used so
far and adding one to it. (There are safer techniques to use if you're in a
multiuser environment with the possiblity of many concurrent insertions)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Michael Conroy said:
Doug, thanks for responding. Maybe I am over complicating the new record
process. I thought using unbound forms was preferred so the data could be
checked beforehand. Also looking for duplicates or making sure a date is a
real date, etc. I guess I could do this in the beforeupdate event.
Regarding
the autonumber, I suppose I was just trying to have a clean table with no
empty records. I guess after a few years of coding I thought binding a
form
to a table seemed like a rookie maneuver and that programming the event
was
better.
Click to expand...
 
Back
Top