469,898 Members | 1,626 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

Verify Existence of Field Names before Opening Form

I need assistance regarding code needed to prevent someone from opening
a form if a table field name(s) has been changed. For example, there is
existing code to check for certain data based on DLookup in a table
before a form can be opened. If one changes the field name in the
table, then the verification check is bypassed. So adding code to
DLookup to check for the existence of specific field names first is
necessary. Any insight will be much appreciated. Thanks.

Nov 13 '05 #1
3 1956
You left out a few details like is the form in question opening up on
application startup or is this form being invoked from another form?

Either way, to check for the existence of a field in a table you can do
this:

Function CheckField() As Boolean
Dim DB As DAO.Database
Dim tdf As DAO.Tabledef, fld As DAO.Field
CheckField = False '--initialize to False
Set DB = CurrentDB
For Each tdf In DB.TableDefs
If tdf.Name = "YourDesiredTable"
For Each fld In tdf.Fields
If fld.Name = "theDesiredField" Then
CheckField = True
Exit Function
End If
Next
End If
Next
End Function

You can place this function inside another function in an autoexec macro
where if CheckField is false - you can display a msgbox stating
something as such. Or you can place this function in the routine that
opens up the next form and also display some msgbox if Checkfield is
false. There may be more efficient code than my CheckField function,
but I don't know it (at this time). Anyway, hope this helps.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #2
Thanks Rich, for the check field Function code. Subsequent to my post,
I did try inserting basic DAO code in the on open form to navigate the
recordset in the other table. Then I changed the name of the field and
received Error 3078 and 64231 and when I changed the name of the table,
Error 3265. I included an ErrorTrap on open form for these and added a
message box when triggered, then DoCmd.Quit. It seems to work okay. Is
this a consistent method of addressing, or does it come with some
risks?

Incidentally, the form is a data entry one only, but checks another
table for the company codes that can be accessed based on a Function
that contains the authorization numbers.

Thanks again, Rolan
Rich P wrote:
You left out a few details like is the form in question opening up on
application startup or is this form being invoked from another form?

Either way, to check for the existence of a field in a table you can do
this:

Function CheckField() As Boolean
Dim DB As DAO.Database
Dim tdf As DAO.Tabledef, fld As DAO.Field
CheckField = False '--initialize to False
Set DB = CurrentDB
For Each tdf In DB.TableDefs
If tdf.Name = "YourDesiredTable"
For Each fld In tdf.Fields
If fld.Name = "theDesiredField" Then
CheckField = True
Exit Function
End If
Next
End If
Next
End Function

You can place this function inside another function in an autoexec macro
where if CheckField is false - you can display a msgbox stating
something as such. Or you can place this function in the routine that
opens up the next form and also display some msgbox if Checkfield is
false. There may be more efficient code than my CheckField function,
but I don't know it (at this time). Anyway, hope this helps.

Rich

*** Sent via Developersdex http://www.developersdex.com ***


Nov 13 '05 #3
Yes, your technique is an option. My experience, however, has been that
it is better to not have an Error Bit turned on (when you get an error).
The application just seems to be more stable if you check for potential
errors rather than letting the error occur. The CheckField function
checks for a potential error and does not let it happen (per the
True/False setting). You can always clear an error with Err.Clear (set
the Error bit off).

Well, trapping for error is always an option, but not letting them occur
is a better option. Of course, you can only do so much predicting.
Depending on how much freedom you give the end users - trapping for
errors may be an option that you have to include.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Stuart Palmer | last post: by
3 posts views Thread by Michael | last post: by
3 posts views Thread by John young | last post: by
5 posts views Thread by Geoff Portbury | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.