Enable All Fields When Creating New Record




I have a problem that I am hoping someone can help me with.

I tried searching for a solution to this problem, but could not come up
with one. Anyway, I have button in access that opens a form and adds a
new record, see code below:

stDocName = "Jobs_Progress_AddEdit"
DoCmd.OpenForm stDocName, , , stLinkCriteria

'Add a new record
DoCmd.GoToRecord , , acNewRec
Forms!Jobs_Progress_AddEdit.cb_job_id = Me.txt_job_id
Forms!Jobs_Progress_AddEdit.txt_worker_pay_rate = dWorkerRate
Forms!Jobs_Progress_AddEdit.txt_supervisor_pay_rate = dSupervisorRate

The form that this opens is not only for adding new records, but a user
can click on an existing record open this form and edit the data (same
exact form).

In some situations, I do not want the user to edit the data, therefore I
enable/disable certain controls on startup, see code below.

Private Sub Form_Current()
Dim ctl As Control
If (Forms!Jobs_Details.cb_status = 1) Or Not IsNull
(Me.txt_applied_invoice_date) Then
For Each ctl In Me.Controls
If ctl.Tag = "disable_1" Then ctl.Enabled = False
Me.bt_Delete.Enabled = False
End If
End Sub

It appears that the first record, in the table I am trying to create a
new record for, meets the criteria to disable the fields when the form is
opened. Once I hit this code, it disables all the fields in my form,
then creates a new record. Now I can't edit the fields I need to when
creating a new record. So the flow looks something like this.

Step 1. In Form A, Users Hits Add Record Button which he is hoping to
put a new value in table X
Step 2. Form B gets launched and brings up the first record in table X
(I guess by default).
Step 3. The Form_Current code gets run (which I really don't want it to
do when the user hits the add new record button)
Step 4. The Form_Current Form has yet to hit the add new record code, so
it is currently on the first record.
Step 5. The code disables the fields I need the user to input.
Step 6. The New record code runs, but all the fields are disabled.

I guess I could make these two separate forms, but I don't see what I
should do that give it’s the exact same data.

I am thinking I need to do 1 of two things....

1. Once the new record gets created Run the Form_Current code again (not
2. Don't run the Form_Current code when creating a new record.

Thanks in advance



Ken Snell \(MVP\)

Change this code line:
DoCmd.OpenForm stDocName, , , stLinkCriteria

to this:
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

This will open the form in "Data Entry = Yes" mode, and will allow only the
addition of new records, and won't let users see existing data records.

Then delete this code line from your code, because it's unnecessary:
DoCmd.GoToRecord , , acNewRec



George Nicholson

You'll probably need to play with it a bit, but maybe this will give you
some ideas. Set a True/False variable then loop thru control collection
once. You need to Loop for every record so that controls get set properly
for each record.

Dim bolEnable as Boolean

If Me.NewRecord Then
' Enable all controls
bolEnable = True
Else If (Forms!Jobs_Details.cb_status = 1) Or Not IsNull
(Me.txt_applied_invoice_date) Then
' Disable certain controls
bolEnable = False
' Default is Enabled. Neither If or ElseIf were True
bolEnable = True
End If

For Each ctl In Me.Controls
If ctl.Tag = "disable_1" Then ctl.Enabled = bolEnable
Me.bt_Delete.Enabled = bolEnable

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question