opening form locks table in access

on my open form event i am checking for the existence of columns in the underlying table:
the ideas was that if they all exist the form can get on and open. If a particular column didn't exist, i had hoped to close the form and create the fields which i tried with Each of the code segments work I am just not sure where to put them so that columns get created when necessary or if the columns exist the form opens.



Private Sub Form_Open(Cancel As Integer)
Dim a, b, c, d, e, f As Boolean
a = False
b = False
c = False
d = False
e = False
f = False

'   Me.Txt_Search_For.SetFocus
    'check fields in exportable and set boolean variables
    If CheckFieldExists("TblExportVinstems", "seq") = True Then a = True
 
    
    If CheckFieldExists("TblExportVinstems", "DoorsExtracted") = True Then b = True
    If CheckFieldExists("TblExportVinstems", "DVLACode") = True Then c = True
    If CheckFieldExists("TblExportVinstems", "CarwebCode") = True Then d = True
    If CheckFieldExists("TblExportVinstems", "VinConcat") = True Then e = True
    If CheckFieldExists("TblExportVinstems", "BodyDescription") = True Then f = True

Open in new window


'check that all tables existed
If a And b And c And d And e And f = True Then
'open form
else

DoCmd.Close

Dim x As Boolean
' FAILS HERE AS FORM IS STILL OPEN AND TBL LOCKED ******************
If a = False Then x = CreateField("TblExportVinstems", "Seq", dbLong)

If b = False Then x = CreateField("TblExportVinstems", "DoorsExtracted", dbLong)

If c = False Then x = CreateField("TblExportVinstems", "DVLACode", dbText)


If d = False Then x = CreateField("TblExportVinstems", "CarwebCode", dbText)


If e = False Then x = CreateField("TblExportVinstems", "VinConcat", dbText)


If f = False Then x = CreateField("TblExportVinstems", "BodyDescription", dbText)
end if

Open in new window

PeterBaileyUkAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Along the same lines as autoexec you can use open event code in a dedicated startup form if you have one. That is my preferred approach, all vba.
0
 
mbizupCommented:
Try using an intermediate form whose purpose is to run this code, close itself and open the other form.

Or start with a blank recordsource and set the recordsource through vba after running that code.
0
 
PeterBaileyUkAuthor Commented:
i was just looking at autoexec but although i created the macro i am not sure how to edit the code
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mbizupCommented:
I don't use macros much but I think there is a runcode command which lets you specify a function.   Your code would have to be placed in a separate function in module outside of your form.
0
 
mbizupCommented:
The startup form of course shouldn't be bound to the table you are changing.
0
 
PeterBaileyUkAuthor Commented:
great thx
0
 
hnasrCommented:
Current form (a) locks table for design.
Current form opens form (b), closes it self, frees table (a) for design.

Form (b) runs code in form_open event  to modify table (a), runs form (a), closes it self.

As a side note:
But why do you need that?
I prefer to separate table design issue from from form design issue. This gives better control over data types and avoid accidental creation of unwanted fields/field names. This complicates database maintenance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.