Clearing a form - having real issues

[mike_980]

Ok, so im making a database to track conveyor belt stops.



I have 5 tables: tblproblemdetail, tblshift, tblstationdetail, tblstops, tbltypeofstop

I then have 2 forms, the first is to add problems to the system (problem, counter measure, type of stop (combobox looking up values from tbltypeofstop) and responable party.

The second form has cascading combo boxes, type of stop and select problem, when a type of stop is chosen, the list in select problem changes accordingly. the form then has other fields such as date, time, sation number, shift and length of stop but i dont think these are causing the problem.

I have two buttons at the bottom of the second form, save and close. The save works fine and the VB code only lets it close when all fields are complete. The "close" button works fine UNLESS type of stop and another field have being filled in. I have tried undo commands, value = null, value = 0, value = "" and the rest of them. I can get the form to clear fine but the autonumber for "stop_ID" still goes into the table and creates a blank record.

I put the stop_ID onto the form to see if I could reset it to the "(new)" value it shows on a new record and whatever I do it always stays a value.

[June7]

The fields are populated by user entry? You want to allow user to Close after entering values but not save that record, essentially aborting the entry? I have never been able to get 'Undo' commands to work right. Only thing that worked for me was to send {ESC} character twice. Sending keystrokes is highly discouraged but I was desperate.

Private Sub btnCancel_Click()
SendKeys "{ESC}", True
SendKeys "{ESC}", True
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

The autonumber will still be 'used' but record should not commit.

[mike_980]

The fields are populated by user entry? You want to allow user to Close after entering values but not save that record, essentially aborting the entry? I have never been able to get 'Undo' commands to work right. Only thing that worked for me was to send {ESC} character twice. Sending keystrokes is highly discouraged but I was desperate.

Private Sub btnCancel_Click()
SendKeys "{ESC}", True
SendKeys "{ESC}", True
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

The autonumber will still be 'used' but record should not commit.

excellent thanks i will try that on monday when im back at work! yeah the user fills out the form and has the option to save and add a new one (saves and adds new record) or close which will hopefully abort the current "add new form"

as long as it prevents blank records im happy because the way my forms work I cant make fields required as it gives errors half way through filling out the form

[mike_980]

doesnt work sorry, anyone else have any ideas?

[June7]

Are the 2 forms a form/subform arrangement?

Do you want to provide project for analysis?

[mike_980]

Thanks for the reply,
The works computer wont let me upload the file so I will upload it from home when I get back. The forms are two seperate bound forms. The "stoppages" form i have issues with is made up of a few raw data fields but it also pulls info from other tables such as stop type, shift and station number.

The "stoptype" combobox requeries a query that updates the ""problemdetail" combobox.

Although "stoptype" is on the form, the control is unbound and the data is simply used to run the query and is not saved into the table, will this be what is causing the issues?

this is my most recent attempt - excuse the ammature control names it's a very long time since I've used access and i'm well out of practice!

Me.Combo47.RowSource = ""
Me.Combo17.RowSource = ""
Me.stoptype.RowSource = ""
Me.Combo49.RowSource = ""
 
Combo47.Value = Null
Combo47.Requery
Combo49.Value = Null
Combo49.Requery
Combo17.Value = Null
Combo17.Requery
lengthofstop.Value = Null
stoptype.Value = Null
stoptype.Requery
Date.Value = Null
Time.Value = Null
DoCmd.Close

I want the close button to basically ensure anything the user has entered in the form is wiped when they click close. I dont mind if there are ways around it, for example clearing the fields then going back to that same record next time when the form is opened from the menu but I can't think how that could be done...

thanks for your help guys!

[ssanfu]

Me.Combo47.RowSource = ""
Me.Combo17.RowSource = ""
Me.stoptype.RowSource = ""
Me.Combo49.RowSource = ""
 
Combo47.Value = Null
Combo47.Requery
Combo49.Value = Null
Combo49.Requery
Combo17.Value = Null
Combo17.Requery
lengthofstop.Value = Null
stoptype.Value = Null
stoptype.Requery
Date.Value = Null
Time.Value = Null
DoCmd.Close

I am curious as to why you are setting the combo box row sources to an empty string?

Save your self some typing... ".VALUE" is not needed because "VALUE" is the default property. So Me.stoptype is the same as Me.stoptype.VALUE.

Some data types can't be set to NULL. But you can set them to "EMPTY".

To eliminate blank records, there are 3 methods I use:

1) Use unbound forms. This can be a lot of work. I rarely use this.

2) When the form opens, set a numeric field to a number that would never be used. When the form is closed, execute a delete query that deletes any record where the field equals that number. For example, when my "Add Hours" form opens, it creates a record for every active employee and enters -1111 for the hours worked. When the form closes, the delete query deletes any record that has hours of -1111.

3) If there is an Autonumber field in the table (recordset), grab it and save it to a hidden unbound control on the form. When the form closes, execute a delete query with the WHERE clause equal to the value in the hidden control.

[mike_980]

I am curious as to why you are setting the combo box row sources to an empty string?

Save your self some typing... ".VALUE" is not needed because "VALUE" is the default property. So Me.stoptype is the same as Me.stoptype.VALUE.

Some data types can't be set to NULL. But you can set them to "EMPTY".

To eliminate blank records, there are 3 methods I use:

1) Use unbound forms. This can be a lot of work. I rarely use this.

2) When the form opens, set a numeric field to a number that would never be used. When the form is closed, execute a delete query that deletes any record where the field equals that number. For example, when my "Add Hours" form opens, it creates a record for every active employee and enters -1111 for the hours worked. When the form closes, the delete query deletes any record that has hours of -1111.

3) If there is an Autonumber field in the table (recordset), grab it and save it to a hidden unbound control on the form. When the form closes, execute a delete query with the WHERE clause equal to the value in the hidden control.

great thanks! I was setting rowsource as a last try! kinda knew it wouldnt work before trying it, never thought about running a delete query I've just been trying to do it though VB on the form I'm sure your method will work!

I'm not sure I understand fully so just to clarify... Make an unbound control which is equil to the autonumber, then use a delete query to find whatever value is in the unbound control and delete the record? excuse my ignorance as i'm not massivly good with access but could the delete query not just pick up the autonumber value if autonumber was added as a hidden control then get access to hold the value, close the form then run the query? (i've just realised while writing this that it wouldnt like it if you ran a delete query before the form was closed!)

[ssanfu]

Make an unbound control which is equil to the autonumber, then use a delete query to find whatever value is in the unbound control and delete the record? excuse my ignorance as i'm not massivly good with access but could the delete query not just pick up the autonumber value if autonumber was added as a hidden control then get access to hold the value, close the form then run the query? (i've just realised while writing this that it wouldnt like it if you ran a delete query before the form was closed!)

Oops, my bad. I meant a bound control.

I put an autonumber field in every table. So I would put a hidden text box on the form (in the detail section- my convention is to set the control bk ground color to pale yellow to indicate it is hidden) that is bound to the autonumber field.

If you do not enter anything in any control in the new record, the autonumber is not generated. Once you type anything in any control, the autonumber is generated. If the cancel button is clicked, the code would:

- check to see if the current record is a new record
- save the autonumber to a variable
- close the form - doesn't matter if the record is saved or not
- executes the delete query.
- optionally activates a form and sets the focus to a control.

You have to be careful using this method. It is real easy to delete the wrong record. ...

[mike_980]

It worked!!! thanks so much for that!