In a form I have about 25 buttons that perform tasks similar to the code below. The problem is, since adding this code the form takes a fair amount more time to open. I have become knowledgeable enough about VBA and forms to be really dangerous--but do not know enough about Access to know if this is the best approach.

My question is, should I be looking at modules or macros or some other method to achieve what I want without slowing my database down. I'm certain that what I am doing can be done in about 20 different ways--but am not sure if it would be different or better. I know zip about the other methods functions (modules, macros, etc.) , thus have no idea how to research them for this application--or more importantly--if they will improve my database or just make it different. Could somebody please point me in a direction so that I can research and learn. Thanks



Private Sub ApplicationPacket_Click()
If MsgBox("Are you sure you want to record and print this?", vbYesNo + vbQuestion) = vbYes Then
APIntroductionPacketSentDate = Date
DoCmd.OpenForm "DocumentationSubformOutgoing", , , "[APIDNumber]=" & Me![APIDNumber]
DoCmd.GoToRecord , , acNewRec
[Form_DocumentationSubformOutgoing]![APIDNumber] = [APIDNumber]
[Form_DocumentationSubformOutgoing]![IDName] = [APIDName]
[Form_DocumentationSubformOutgoing]![Outgoing] = "Outgoing"
[Form_DocumentationSubformOutgoing]![DocumentDate] = Date
[Form_DocumentationSubformOutgoing]![Document] = "Application Packet Sent"
DoCmd.Close
Me.Refresh
DoCmd.OpenReport "AdoptiveParentPREMATCHApplicationPacket", , , "[APIDNumber]=" & Me![APIDNumber]
DoCmd.OpenReport "Envelope", , , "[APIDNumber]=" & Me![APIDNumber]
End If
End Sub

How slow is your database? Exactly when does this slowness occur and what did you add to the database that appears to be causing it? Can you post the database?

Robert Crouser

The front end is about 13 mb--too big to post. It only slows down when I try to open the form that contains the code I posted previous. The code you see is repeated about 20 times (similar code but with differences that can't be streamlined). Prior to adding the new code, the form opened up in reasonable time. The form is not terribly slow now--but noticiable difference to open. The code is not complex, but it made me think that maybe there should be a different approach???

How slow is it exact.y? And what are the 20 buttons (list each of them).

Probably takes about 5-6 seconds to open. All of the buttons have code similar to the code posted. They each carry out the same functions. Should I be putting this code into a module??? Would that help the form to open quicker??

I don't think 5-6 seconds is very long. Comment out the code and try it.

You didn't list what the buttoms are for.

From your one example it looks like you are opening a form just to
create a new record by filling textbox controls with values from
another form. Instead of opening the form you should probably just
be using an SQL Insert statement or an empty recordset based on
the table you're adding records to.

Open a code module, click Help and search for AddNew Method

I agree that the opening of additional forms to insert data is cumbersome--only way I knew how to do it. Is an SQL Insert the way to go?? I will do a search on that subject to see if that can remedy my issue.

Forms update tables automatically. You may not need to code an insert.