problem running Macro

[amauricio2]

Hello everyone this is my first time up to bat, and I hope someone can help.... please. I have users clicking a command button that runs a macro that populates several tables with a Social, no problem but when I have multiple users I get this message "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later", is there any way override that message, I don't want them to get this message every time? I hope i am emailing the right Forum if not I would appreciate some direction

 

[Onyxpurr]

In my experience this only happens when I am changing the design of the database, such as a form or report. Are you changing the design in anything or just field values?

 

[amauricio2]

No all the Command does is run the macro to open several forms then sets a value to create a new record in each form, the Macro closes the forms and returns to the original form. So I am not designing anything just populating data. hey thanks for replying, I really appreciate it.

 

[MakeItSo]

Try some Code instead of that macro. With this, you can avoid having to open forms - and it might solve your problem:

Sub YourCommand_Click
Dim sSQL as String

sSQL="INSERT INTO YourTable (Field1, Field2, ...) VALUES (Value1, Value2, Value3)"
^
DoCmd.RunSQL sSQL

End Sub

You must of course check, which SQL statement will suit your needs. It might also be one like this:
INSERT INTO Your target table(Field1, Field2, ...)
SELECT Field1, Field2...
FROM Sourcetable

Is your SQL good enough?
If you need complete code, I would require table and field names and what values will be added..


OK?
MakeItSo
WHERE Suchbedingung

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[amauricio2]

Allright I tried that I am am not quite getting. So let me tell you what the tables and fields are and maybe you can help me on this one MakeItSo.
First I am populating a Demographic form with all demographic information to include a Social. So from the Command click the macro runs to create a record of the Social to the following tables
tblReferral(actually form in the macro)
tblInsurance (actually form in the macro)
tblComments (actually form in the macro)
tblPlan (actually form in the macro)
tblCertification (actually form in the macro)
tblBilling (actually form in the macro)
what do you think is this possible? Thanks alott

 

[MakeItSo]

Is your "Social" one value (like socialID)? Or is it a set of values with age, marital status a.s.o.?

Here's one possible solution for 1 value:
Dim i as Integer
Dim myTables as Variant

myTables = Array("tblReferral", "tblInsurance", "tblComments", "tblPlan", "tblCertification" ,"tblBilling&quot

For i = 0 to UBound(myTables)
sSQL = "INSERT INTO " & myTable(i) & " (YourSocialField) VALUES ('" & Me.YourSocialOnTheForm & "')"
DoCmd.RunSQL sSQL
Next i

This should help you get started.
Anyway, I have the feeling, we will have to eat slowly through this cake...

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[amauricio2]

That did it!! I can't believe it thanks alot you solved more than just the problem I initially had, Thank you very much MakeItSo