Running a Query(NOT Manually)

[lynsey2]

this might sound Mad but I know that to run a query you can either choose run from the query menu or run on the query design toolbar

but how do you get it to run automatically

eg.

I have a form with Print script on it and simply wanted to add some code in there so on click the printscript button 1.Runs the Append Query 2.Prints out the script

I have tried this and it doesnt like it!! I have also tried creating a miscellaneous button - run query, and that seems to make 2 of my calculated feilds go NAME??and not work either?

I might just be missing something simple so any hints would be great sorry to bother you

Thanks in advance

 

[cogent1]

You can either make your button run a macro that opens the Query (OpenQuery option) or add a DoCmd.OpenQuery to the ON_Click event of the button. But what's this script you're on about? Where does that fit in?

 

[lynsey2]

My form has a print button (print Script)
Instead of having another button run Query I wanted to add code to the print script button so that 2 things get done at once

Heres the code i have:

Private Sub PrintScriptmeth_Click()
If Supervisedcheck = True Then
Text94 = Start_Date
End If

On Error GoTo Err_Run_Append_MethStore_Click

Dim stDocName As String

stDocName = "Append MethStore"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Run_Append_MethStore_Click:
Exit Sub

Err_Run_Append_MethStore_Click:
MsgBox Err.Description
Resume Exit_Run_Append_MethStore_Click

On Error GoTo Err_PrintScriptmeth_Click

Dim MyForm As Form

stDocName = "Methadone Script"
Set MyForm = Screen.ActiveForm
DoCmd.SelectObject acForm, stDocName, True
DoCmd.PrintOut
DoCmd.SelectObject acForm, MyForm.Name, False

Exit_PrintScriptmeth_Click:
Exit Sub

Err_PrintScriptmeth_Click:
MsgBox (Error)
Resume Exit_PrintScriptmeth_Click


End Sub

DOesnt like this and I cant see why

 

[cogent1]

You have , by the look of it , jammed two procedures together. OI! LYNSEY!NO!
try this......
Private Sub PrintScriptmeth_Click()

On Error GoTo Err_PrintScriptmeth_Click()

Dim stDocName As String
Dim MyForm As Form

If Supervisedcheck = True Then
Text94 = Start_Date
End If


DoCmd.OpenQuery "Append MethStore"
'acNormal and acEdit are Defaults.

stDocName = "Methadone Script"
Set MyForm = Screen.ActiveForm
DoCmd.SelectObject acForm, stDocName, True
DoCmd.PrintOut
DoCmd.SelectObject acForm, MyForm.Name, False

Exit_PrintScriptmeth_Click:
Exit Sub

Err_PrintScriptmeth_Click:
MsgBox Err.Description
Resume Exit_PrintScriptmeth_Click


End Sub

 

[cogent1]

....and why don't you just open the form to print it? Why use SelectObject etc..

 

[lynsey2]

HI i tried that code and still got exactly the same message I just dont get it!!

I knew that the code I had was wrong but it seemed the closest to getting it to work

the error message is saying something about key/lock and valadation rule Violations and type conversion failure it says click yes to ignore the errors and append the stuff anyway but it doesnt do it

 

[lynsey2]

Jamming 2 procedures 2gether....oops
Me bad...kinda just about worked though

 

[cogent1]

OK. The message you are getting is because you are doing something really evil, like trying to append records with duplicate primary key fields or no primary key fields or summat similar . Give me the EXACT message, then I can tell you what's amiss.

How many records is the query appending? To which table? What sort of data is it? What happens if you do run the query manually?

 

[cogent1]

Wouldn't care to fly in a plane that "just about worked", though!

 

[lynsey2]

hey sir Im new at all this I am just used to designing stupid little moc car rental systems n stuff
I seem to be realising that well lets just say im not verry good at this.....
Anyway I solved that error message problem(pitty I dont know how)
whats happening now is (wait 4 it)
I have 2 tables

1 tep that only holds 1 patients preSCRIPTion record(this changes to the current one on the form)

1 master that holds all preSCRIPTion records done

My append query is so that the tep table updates the master table with the new preSCRIPTion (only if the doc doesnt change it eg if the doc makes a mistake and goes to change it the data is stored in the tep table) once he/she is sure that the info is correct and PRINT SCRIPT (as in prints the patients prescription) the temp table updates the master table with the correct record.

at the mo this happens but the master table only gets updated when a new preSCRIPTion is made.. so its like running a record behind

now if you can work out what i am tring to say im sending you to my ex english teacher to tell him to give me my higher english pass.

 

[cogent1]

Much to my amazement, I do understand what you're saying.


1) You make a temporary record and store it in a one-record table
2) When that record is verified, you add it to a master table.
3) You print a script based on the newly-added record.

My next question:

Why store the unverified record in a table at all!

How long is it when the record is entered before it is verified?. If it's just a question of the doc scanning it over for accuracy and being able to edit it before committing it to the printed copy , WHY NOT JUST PUT IT IN A TEXT BOX? Then it can be added to the master table and you can then just print the last record..

 

[lynsey2]

yep that would make sence I didnt think of it like that

em.. you know what I think that would make a lot more sence ill do that instead THANX

 

[cogent1]

OK. Are we done here? Just make up a small form with an unbound text box and a similar text box bound to your table field. When the doc verifies the details, use a VERIFY button to copy the contents of the unbound box to the bound one. This updates the Master table automatically, no query needed. Good luck, email me if stuck.

 

[lynsey2]

yep i will Thanx 4 all your help..im sure ill speak 2 you again soon more than likely