Form Help

[Daytrder1]

I have a form where users enter data. Some of the data is unknown at time of entry and the fields are left blank. Sometime in the near future, the user goes back in, finds the record (using a control number) and enters more data into the fields that were left blank at the time of original entry. My question is how do I lock the form in a way that only fields that contain no data currently can be edited? What we don't want is the users going back in and changing information that was already entered earlier (i.e. dates, time, numbers, etc). We only want them to be able to add data to a record. Thanks in advance for your assistance.

 

[The Stoat]

I have a form where users enter data. Some of the data is unknown at time of entry and the fields are left blank. Sometime in the near future, the user goes back in, finds the record (using a control number) and enters more data into the fields that were left blank at the time of original entry. My question is how do I lock the form in a way that only fields that contain no data currently can be edited? What we don't want is the users going back in and changing information that was already entered earlier (i.e. dates, time, numbers, etc). We only want them to be able to add data to a record. Thanks in advance for your assistance.

I'd do this in VBA. Not sure how much you know so i'll start from basics

The form has an event called OnCurrent. This is activated every time the user moves from one record to another record. So if you have 10 records and the user moves from record 1 to record 9 -for example- this event is activated. What you can do is write some code to check the contents of the objects on your form like text boxes. If there is something in it you can set the boxes properties to locked. This will prevent the user from entering anything else in it.

So first things first. Open the form in design view and select the form properties. Under the tab marked events there will be the On Current event procedure right at the top. Click the three little dots on the grey button on the right of the row. Then select "code builder". This will let you write the VBA.

Right at the top of the code form under Option Compare Database make sure there is written. option explict . if not write it in.

Private Sub Form_Current()
Dim Ctl as Control 'create a variable for the forms controls
Dim Frm as Form ' create a variable for the form

Set Frm = Me ' Sets the variable to the current form

For Each Ctl in Frm.Controls 
'A form has a collection of controls i.e. all the controls that are on the form 
'text boxes, labels, comboboxes etc. We are asking to go through them one 
'at a time.

On error resume next 
' Not all controls have the following text property an error will be caused if 
' it tries to do this on a command button for example
' this line just asks it to ignore the error and go to the the next line of code

If Ctl <> 0 or Ctl <> "" Then 
' Checks to see if there is a value in the ctl. This may cause problems if the
' value the user enters is supposed to be 0. This is also the default value for
' a numeric field. Equally if  you have set the form up to display other default 
' values then this will not work properly

Ctl.Locked = True ' Lock the control so the user can't change the data

else

Ctl.Locked  = False ' Otherwise unlock the control

end if

Next Ctl ' Moves onto the next control

set Frm = Nothing ' Empty the Frm variable - good practice 

End Sub

Hope that helps

The Stoat.

 

[Daytrder1]

Thanks

Yes, that worked as I hoped it would. Now could I ask one more thing? How can I modify that code in order to exclude certain fields from that, as an example a filed called "notes". I would like that field to be updated anytime and never locked. Thanks again!!

 

[The Stoat]

Yes, that worked as I hoped it would. Now could I ask one more thing? How can I modify that code in order to exclude certain fields from that, as an example a filed called "notes". I would like that field to be updated anytime and never locked. Thanks again!!

The textbox for the notes field will be called something like TxtNotes i guess. So you try this. It should check the name of the ctl as well as the contents.


if Ctl.text <> 0 or Ctl.text <> "" and Ctl.Name <> "TxtNotes" then etc...

The Stoat

 

[Daytrder1]

Thanks again

Sure, nice and simple......thanks so much!!

 

[The Stoat]

Any rabbits gratefully accepted

 

[Maailmapoiss]

Thanks

Thanks with your example and little changes and it helped me too.