PLS TELL ME WHAT IM DOING WRONG!Need DataEntry form to addrecord AFTER pressing SAVE!

[Ignoth]

It sounds so simple, yet I've been banging my head over this for over a week now and it's driving me insane! Can someone please tell me what I'm doing wrong?

I have only 1 master table and am trying to create a data entry form form based on some of those fields. The way I have it set up right now is with my form bound to the table, but I want to create some space between it from being automatically saved as entered, by needing to press the "Submit Request (SAVE/ADD RECORD)" button. I also need to validate that 2 fields are NOT NULL before being able to add the record...Need to ensure referential data integrity.

WHAT I'VE DONE SO FAR:

SWITCHBOARD- "click form" (runs macro to:" Open- Form, Go To- RecordNew")

FORM- has a macro set to validate fields: "If- field is null, cancel event, msg box, go to control." <- this works fine

SAVE BUTTON- I tried getting around the issue of a "SAVE" button by adding a macro to the button to "GoTo- NEW RECORD" which would just create a new record to start entering data into (creating the illusion that it just saved by refreshing and going to the next record).
But this is creating problems for me, because when I press the SAVE button, I get the error: "You cannot go to the specified record, because you may be at the end of a record set." then I get the macro error pop up stating the macro "Argument -1, , New'' [Stop All Macros]

(NOTE ALSO) - Each record is time-stamped, and so pressing "Save" (going to "NEW RECORD") is creating ANOTHER new time-stamp upon the next new record, creating false data.


How do I correct this issue?! I feel like I'm going about this the wrong way.

 

[Isskint]

2 methods jump to mind, both involving using an unbound data collection form. Gather the data, run your validation checks and if everything is fine you can either:

• Run SQL to add record to your table

OR

• Run an add record query that pulls its data from your unbound form.

 

[missinglinq]

First off, you're using all caps in your thread title, which is a very poor way to make friends and influence people; in forums this is considered to be shouting.

Secondly, instead of using Access in the way it is intended to be used, you're trying to re-invent the wheel here, for no valid reason that I can see, and creating problem after problem after problem for yourself.

There is a no reason for a 'Save button' in Access, and no reason to use Unbound Forms for data entry.

To Save a Record you simply have to leave the Record; you can

1. Move to another Record
2. Go to a New Record
3. Close the Form or even Access itself.

If you need to Validate one or more Fields, either for content or to insure that they actually have content, i.e. they aren't empty, you do that through code in the Form_BeforeUpdate event. When the user does any of the above three things, Access executes the Form_BeforeUpdate event. If your Record fails the Validation, you simply

• Set Cancel = True, to prevent the Record from being saved
• Pop up a Messagebox explaining the problem to the user
• Set Focus back to the offending Control so that the problem can be corrected

You can do this for as many Fields/Controls as is necessary. Here's a simplified example:

Private Sub Form_BeforeUpdate(Cancel As Integer)

 If Nz(Me.Control1,"") = "" Then
   MsgBox "Control1 Must Not Be Left Blank!"
   Cancel = True
   Control1.SetFocus
   Exit Sub
 End If
 
If Nz(Me.Control2, "") = "" Then
   MsgBox "Control2 Must Not Be Left Blank!"
   Cancel = True
   Control2.SetFocus
   Exit Sub
 End If

End Sub

Your Time Stamp should also be generated in the Form_BeforeUpdate event, after all data has been Validated and corrected, if necessary.

I'm not really sure what you mean by

The way I have it set up right now is with my form bound to the table, but I want to create some space between it from being automatically saved as entered...

If you want to ask the user if they're sure that they want to Save the Record, this, too, can be done in the Form_BeforeUpdate event. If you mean something else, explain the above a little better and we'll try to address that for you.

Access can be overwhelming to a newby, and unfortunately, Microsoft's attempt to ease this problem, through the use of Macros, really hasn't made things easier at all! Macros are rigidly structured, and limited in the things that they can do.

That's why, with the exception of a specialized Macro named AutoExec, experienced developers never use them. Because of this, you'll only be able to get limited help here (or in any Access forum) if you use continue to useMacros, and would be far better off learning VBA.

Linq ;0)>

 

[Ignoth]

Sorry about the caps everyone... my frustration made my distress call unnecessarily extreme!

Missingling, you work at Genentech? I'm temping right now at Genentech in San Diego! (I finished my assignment way ahead of schedule, so they told me to build a database for them, and I have zero experience and dont' really care to learnlol.)

I've been trying to avoid learning VBA and SQL by using the macro's. I feel like I'm pretty much done with the database after ironing out the validation/time-stamp/save (go to new record) form, but my I've been playing problem "wack-a-mole"with my current set-up/macros. I'll learn it if I have to, but if I could be walked through this like a baby through the regular macro-system, that would be preferable, because I just want to get the job done.

The form is for new requisitions, and for aesthetic/end-user functionality purposes I wanted to just have a "Submit Request" button to refresh the form (go-to-new-record?).

The problem I'm having in Form_Before_Update is that if the first field is filled, but the second isn't, it will still allow the record to be "saved" upon "Submit Request" (Macro- Go To- New Record ). If both fields Is Null, nothing will happen upon pressing the "Submit Request" button. If only the second field ([Sort #]) is Is Not Null, but the first field is Null, it will prompt: (MsgBox) "Please fill in the first field", then ANOTHER MsgBox prompt (information error) "You cannot go to the specified record: You may be at the end of a record set", crash the "Submit Request" button "frmNewPartsRequest : Submit Request btn : OnClick : Embedded Macro. Action Name: GoToRecord Argument-1, New.... [Stop All Macros]

This is my exact Form_Before_Update macro, maybe there is something wrong with it:

If Forms![frmNewPartsRequest]![Requestor]! Is Null Then
Cancel Event
Msg Box - "Please fill in the Requestor field"
Go To - Control Source - Requestor
-If Forms![frmNewPartsRequest]![Sort #]! Is Null Then
Cancel Event
Msg Box - "Please provide the Sort #"
Go To - Control Source - Sort #

Here is my Control_Button on the form:
(Macro) Go To - Record - New

How would I correct this issue and apply the timestamp in the Before_Update?

Thanks!

 

[Ignoth]

h8mylife...

 

[ghudson]

In the design of the table you can set the "Required" property to Yes to ensure all of the required fields are completed before the record can be saved.

The A better mouse trap? sample might give you some ideas on how to ensure the user does what you want before they can save the record.

 

[Ignoth]

Ghudson, I took a look at your file but I'm a newb and don't understand VBA so... But I required those fields, but the problem is with the same problem I keep running into is with my "GoTo-NewRecord" button after trying to submit an incomplete (if one of the two fields isn't validated) record...

"You cannot go to the specified record. You may be at the end of a recordset" msg box prompts and then [Stop All Macros] box comes up.

^ I don't want the user to encounter this error and then have to hit "Stop all macros", etc...

I also want to put a Now() timestamp when the record is created without showing the field in the form and am not really sure how to go about applying this.

 

[Ignoth]

the problem i encountered with Now () is that my switchboard opens the form up "as new record" and so it automatically creates the timestamp upon the form opening, and not when its actually adding/(updating) a record to the table-- so when I press "submit request (goto-newrecord)" it creates another NEW timestamp starting another new record without any fields filled creating faulty records...

How do I correct this?

 

[Pat Hartman]

If empty records are being created just because you navigate to an empty record, you have code or a macro that is dirtying the form. You'll need to find this code and remove it. To populate a "LastUpdateDate" field, do it in the FORM's BeforeUpdate event
Me.LastUpdateDate = Now()