Only Allow Field Edit When Blank And With Specific Content?

[schof99]

Hi there,



I have lurked around this site for some time now and always been able to find answers in previously asked questions. However, I can't seem to figure this one out.

Apologies if this a little long winded but I want to make sure I give you enough information so that you understand my problem.

I have two tables:

tblProviders which contains fields - AlphaID (primary key), LastName, FirstName, Specialty.
tblTransfers which contains fields - AccNumber (primary key), TDate, TDestination, Service, AlphaID, Reason

tblTransfers is linked to a .txt file that is updated daily by the software we use at work and the records are appended to the table.

Sometimes the AlphaID is not available in the .txt file as it has not been documented in the other software we use. I have a form that users will open to fill in the Reason field and, when needed, the AlphaID.

I need the AlphaID field to be locked down so that a.) it can only be completed when it is empty and b.) it can only accept an AlphaID from tblProviders.

So far I have:

Private Sub AlphaID_GotFocus()

If Nz(Me.AlphaID, "") <> "" Then
AlphaID.Locked = True
Else
AlphaID.Locked = False
End If

End Sub

This works great for only allowing the field to be edited if it is blank.

I also have a combobox that can be used to select a provider and the following code to establish the AlphaID for that provider:

Private Sub cboProviderLookup_AfterUpdate()

Me.AlphaID.Value = Me.cboProviderLookup.Column(0)

End Sub

So, I can do each of the tasks seperately. What I am struggling with is how to combine these two things so that AlphaID can only be edited when the record is blank and can only accept an AlphaID from tblProviders.

I would greatly appreciate any pointers you can offer, thanks.

[June7]

Should probably use Conditional Formatting to enable/disable the control.

Use a combobox that lists the values from tblProviders and set LimitToList property as Yes.

[John_G]

Another option would be to create a query which only selects records from tblTransfers where Alpha_ID has no value: AlphaID is Null

and use the query as a recordsource for an update form. To assign an ID, you can use a combo box bound to the AlphaID table field to avoid the VBA as you have it above.