?
Solved

opening form locks table in access

Posted on 2013-02-02
7
Medium Priority
?
163 Views
Last Modified: 2013-02-02
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

0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38846613
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
 

Author Comment

by:PeterBaileyUk
ID: 38846620
i was just looking at autoexec but although i created the macro i am not sure how to edit the code
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38846626
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38846629
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38846632
The startup form of course shouldn't be bound to the table you are changing.
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 38846644
great thx
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38846647
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question