Run Query from 2 forms 2

[puforee]

In my DB I have two Forms that I work with. One form shows training by selected instructor. The other shows training for All instructors.

On the first form I have buttons that cause update queries to run. Each query is dependent on 2 field values from the form..Employee number...and Unit assigned to.

Now, the users have asked for the second form (All instructors) to show, so they can page through all the instructors without having to select individuals.

But...I want the buttons on the second form to compute the same update values it does for the first form.

Question: Is there a way to have dependent data from two different forms control a query. Like "FormA employee number Or FormB employee number" I have tried this, with the proper syntax of course, and it still askes for the first forms information. I have lots of buttons on each form and really do not want to create more queries.

Thanks,

 

[Andrzejek]

You can add a standard Module to your Project, set up a Public Sub and run your quesry from that place:

Option Explicit

Public Sub RunMyQuery(ByRef intEmplNo As Integer, ByRef intUnit As Integer)[green]
    'run your query here[/green]
End Sub

You can access this Sub from anywhere (any of your Forms) in your app, just pass the parameters to it.

Call RunMyQuery(txtEmployeeNo, txtUnitAssignedTo)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

It would have been easier for us to answer if you had provided the SQL view of your query. I will often just execute the SQL statement or possibly modify the SQL property of the query and then execute.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Here it is: (and note there are only the two fields from the first form as dependencies for the query)

SELECT tbl_Models_Data.BEMS_Id, tbl_Models_Data.Model, tbl_Models_Data.[Base Month], tbl_Models_Data.[Last PC], tbl_Models_Data.[PC Due], tbl_Models_Data.[Last IP Eval], tbl_Models_Data.[IP Eval Due], tbl_Models_Data.[Last RT], tbl_Models_Data.[RT Due], tbl_Models_Data.[Last ITC], tbl_Models_Data.[ITC/TCE-Month Due], tbl_Models_Data.[ITC Due], tbl_Models_Data.[Last TCE], tbl_Models_Data.[TCE Due], tbl_Models_Data.[Last LOBS], tbl_Models_Data.[LOBS-Month Due], tbl_Models_Data.[LOBS Due], tbl_Models_Data.[Last QA Eval], tbl_Models_Data.[QA-Month Due], tbl_Models_Data.[QA Eval Due], tbl_Models_Data.[Last GS 1st], tbl_Models_Data.[GS Due 1st], tbl_Models_Data.[Last GS 2nd], tbl_Models_Data.[GS Due 2nd], tbl_Models_Data.[Last MOLITSIM], tbl_Models_Data.[MOLITSIM-Month Due], tbl_Models_Data.[MOLITSIM Due], tbl_Models_Data.[StartMonthITC/TCE], tbl_Models_Data.[EndMonthITC/TCE], tbl_Models_Data.[Last MOLITAural], tbl_Models_Data.[MOLITSIMAural-Month Due], tbl_Models_Data.StartDateMOLITAural, tbl_Models_Data.EndDateMOLITAural, tbl_Models_Data.LastDayMOLITAural, tbl_Models_Data.[MOLITSIMAural Due], tbl_Models_Data.[StartMonthPC/IP], tbl_Models_Data.[EndMonthPC/IP], tbl_Models_Data.[LastDayPC/IP], tbl_Models_Data.StartMonthRT, tbl_Models_Data.EndMonthRT, tbl_Models_Data.LastDayRT, tbl_Models_Data.StartMonthRT, tbl_Models_Data.EndMonthRT, tbl_Models_Data.LastDayRT, tbl_Models_Data.[StartMonthITC/TCE], tbl_Models_Data.[EndMonthITC/TCE], tbl_Models_Data.[LastDayITC/TCE], tbl_Models_Data.StartDateLOBS, tbl_Models_Data.EndDateLOBS, tbl_Models_Data.LastDayLOBS, tbl_Models_Data.StartDateQA, tbl_Models_Data.EndDateQA, tbl_Models_Data.LastDayQA, tbl_Models_Data.StartDateGS1, tbl_Models_Data.EndDateGS1, tbl_Models_Data.LastDayGS1, tbl_Models_Data.StartDateGS2, tbl_Models_Data.EndDateGS2, tbl_Models_Data.LastDayGS2, tbl_Models_Data.StartDateMOLITSIM, tbl_Models_Data.EndDateMOLITSIM, tbl_Models_Data.LastDayMOLITSIM, tbl_Models_Data.TCE, tbl_Models_Data.TCI, tbl_Models_Data.TCE_Date, tbl_Models_Data.TCI_Date, tbl_Models_Data.[GS1 Month Due], tbl_Models_Data.[GS2 Month Due], tbl_Models_Data.Training_Year, tbl_Models_Data.[GS1 Month Due], tbl_Models_Data.[GS2 Month Due], tbl_Models_Data.[Last RT1], tbl_Models_Data.StartMonthRT1, tbl_Models_Data.StartMonthRT1, tbl_Models_Data.[RT Due1], tbl_Models_Data.[RT Sch1], tbl_Models_Data.EndMonthRT1, tbl_Models_Data.LastDayRT1
FROM tbl_Models_Data
WHERE (((tbl_Models_Data.Model) Is Not Null));

 

[dhookom]

puforee,
That SQL statement has no references to any controls on any forms. What do you mean by "only the two fields from the first form as dependencies for the query"?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Wow...I am sorry...wrong query. Here is a correct one.

UPDATE tbl_Models_Data SET tbl_Models_Data.[QA Eval Due] = [tbl_Models_Data]![QA-Month Due], tbl_Models_Data.StartDateQA = DateAdd("m",-1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), tbl_Models_Data.EndDateQA = DateAdd("m",+1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), tbl_Models_Data.LastDayQA = DateSerial(Year([QA-Month Due]),Month([QA-Month Due])+2,0)
WHERE (((tbl_Models_Data.BEMS_Id)=[Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![BEMS_Id]) AND ((tbl_Models_Data.Model)=[Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![AC]));

The Where statement is the place I need to have the query look at two forms. It is showing the one form now...the other form would be All_Instructor Training Setup. The two field names would be the same as the first form.

 

[Andrzejek]

Wow, At least 78 fields in this table.

Wouldn't be easier to just do:
[tt]
SELECT tbl_Models_Data.*
FROM tbl_Models_Data
WHERE Model Is Not Null;[/tt]

And what do you do with the outcome of this SQL once you have it?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[puforee]

What I am doing is updating a table that holds training dates. Each Training evolution has sever dates. The all have specific off sets from a master date. When I change the master date on the form...the button or Drop Down list will cause this query to be executed. The query updates the table and the form is refreshed showing all the new dates. But, as I said in my original Queston above, I have two Forms that can initiate this action..and I want the forms to run the same query...passing the two fields to the query.

So, in the query, is there a way to relate to the both forms. I have tried Form1field1 or Form2field1 and it does not work. If I initiate from Form two the query errors and askes for the Form1 data...and visa versa.

It sounds like I will have to duplicate my queries and change the form references to Form2 on the new query. I was trying not to do that.

 

[dhookom]

I would create a function in a standard module that accepts the BEMS_ID and Model.

Public Function UpdateTrainingDates(lngBEMS_Id As Long, strModel As String)
    Dim strSQL As String
    Dim db As DAO.Database
    Set db = CurrentDb
    [COLOR=#4E9A06]' the following assumes BEMS_ID is numeric and Model/AC is text[/color]    
    strSQL = "UPDATE tbl_Models_Data SET tbl_Models_Data.[QA Eval Due] = [tbl_Models_Data]![QA-Month Due]," & _
        "tbl_Models_Data.StartDateQA = DateAdd('m',-1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & _
        "tbl_Models_Data.EndDateQA = DateAdd('m',+1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & _
        "tbl_Models_Data.LastDayQA = DateSerial(Year([QA-Month Due]),Month([QA-Month Due])+2,0) " & _
        "WHERE BEMS_Id= " & lngBEMS_Id & " AND Model= """ & strModel & """ "
    db.Execute strSQL, dbFailOnError
    MsgBox "Data updated"
 End Function

You can call this function from anywhere by sending the appropriate argument values.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I would try:

Option Explicit

Public Sub UpdateQuery(ByRef [red]intBEMS_Id[/red] As Integer, ByRef [blue]intModel[/blue] As Integer)

strSQL = "UPDATE tbl_Models_Data SET " & vbNewLine _
& " [QA Eval Due] = [QA-Month Due], " & vbNewLine _
& " StartDateQA   = DateAdd('m',-1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & vbNewLine _
& " EndDateQA     = DateAdd('m',+1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & vbNewLine _
& " LastDayQA     = DateSerial(Year([QA-Month Due]),Month([QA-Month Due])+2,0) " & vbNewLine _
& " WHERE BEMS_Id = " & [red]intBEMS_Id[/red] & vbNewLine _
& " AND Model = " & [blue]intModel[/blue]     
[green]
'Execute strSQL here
[/green]
End Sub

and to use it, try from your Form1 (or Form2):

Call UpdateQuery([red]BEMS_Id[/red], [blue]AC[/blue])

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[puforee]

Ahhh working with strSql. Had not thought about that one.
And, yes Bems_ID is numeric and AC is Text.
So the passing of the BEMS_ID and AC/Model data from the form to the SQL is automatic because the Button is calling the Function? I am a little fuzzy on this.
I will give these a try. Is there really any difference between Andrzejek's code and dhookom's.

Thanks,

 

[puforee]

OK...tried running Andrzejek's code. I am having a problem with the Call.

First. Code is in a Class Module called Class1. I copied it from from above.

So, for my QA DropDown object on my form I set the after update event to "Call UpdateQuery(BEMS_Id, AC)". Whent I update the DropDown I get:
Compile error: Sub or Function not defined. The Call shows like this Call UpdateQuery(BEMS_Id, AC)".


What am I doing wrong please.

Thanks,

 

[Andrzejek]

Mine and dhookom's examples are pretty much the same (I like to think about it as ‘great minds think alike’ )

You do have 2 controls on your Form named: BEMS_Id and AC, right?

try and see this:

MsgBox " BEMS_Id value is: " & BEMS_Id & ", and AC value is " & AC

Call UpdateQuery(BEMS_Id, AC)

See what you get in the message box.

If Model is text, change to:

[tt]
Public Sub UpdateQuery(ByRef intBEMS_Id As Integer, ByRef [blue]strModel As String[/blue])
...
& " AND Model = '" & strModel & "'"
[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

Don't use Class Module, we both mentioned a Standard Module. or just a Module. That may be a part of your problem.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[puforee]

OK, I changed the module.

My call code is attached to the Form DropDown object "After update" Event.
The complete code for the after event is: Call UpdateTrainingDates(Bems_ID, AC)
Does there need to be something else?


It is calling the process because I am getting the msg that data has been updated.
But, data is not being updated.

Here is the code that is called:
Public Function UpdateTrainingDates(lngBEMS_Id As Long, strModel As String)
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
' the following assumes BEMS_ID is numeric and Model/AC is text
strSQL = "UPDATE tbl_Models_Data SET tbl_Models_Data.[QA Eval Due] = [tbl_Models_Data]![QA-Month Due]," & _
"tbl_Models_Data.StartDateQA = DateAdd('m',-1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & _
"tbl_Models_Data.EndDateQA = DateAdd('m',+1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & _
"tbl_Models_Data.LastDayQA = DateSerial(Year([QA-Month Due]),Month([QA-Month Due])+2,0) " & _
"WHERE BEMS_Id= " & lngBEMS_Id & " AND Model= """ & strModel & """ "
db.Execute strSQL, dbFailOnError
MsgBox "Values are: " & Bems_ID & ", and AC value is " & AC
MsgBox "Data updated"
End Function

Note: the MsgBox "MsgBox "Values are: " & Bems_ID & ", and AC value is " & AC"

It returns this as a message: Values are:,and Ac value is

It seems that the procedure is running but not doing anything because it may not be getting the BEMS_ID and AC information from the call.

How does the call "Call UpdateTrainingDates(Bems_ID, AC)" know the values of BEMS_ID and AC?

Since the code is running I have a feeling I am just missing a little.

Thanks,

 

[dhookom]

What is the code in the after udpate event? Did you just set the property or use the code/vba window?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Code VBA window. For the Drop Down object - Event after update I selected code and put the call code there. But my question is how does the call code ......(BEMS_ID, AC) know what the values are? I even tried setting it this way Call.........(Me.BEMS_ID, ME.AC) thinking the Me statement would pick up the information from the Form. No go...still nothing updated.

Thanks for your patience.

 

[Andrzejek]

You are mixing and confusing variables from mine and other samples. Stick to just one.

Do you have [tt]Option Explicit [/tt]in your Module? You should have it.

try this:

Public Function UpdateTrainingDates([red]lngBEMS_Id[/red] As Long, [blue]strModel[/blue] As String)
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb

MsgBox "BEMS_Id Value is " & [red]lngBEMS_Id[/red] & ", and Model value is " & [blue]strModel[/blue]

' the following assumes BEMS_ID is numeric and Model/AC is text
strSQL = "UPDATE tbl_Models_Data SET tbl_Models_Data.[QA Eval Due] = [tbl_Models_Data]![QA-Month Due]," & _
"tbl_Models_Data.StartDateQA = DateAdd('m',-1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & _
"tbl_Models_Data.EndDateQA = DateAdd('m',+1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & _
"tbl_Models_Data.LastDayQA = DateSerial(Year([QA-Month Due]),Month([QA-Month Due])+2,0) " & _
"WHERE BEMS_Id = " & [red]lngBEMS_Id[/red] & " AND Model = '" & [blue]strModel[/blue] & "'" "

db.Execute strSQL, dbFailOnError

MsgBox "Data updated"

End Function

Run it and see what you get in a message box.
If you see: "BEMS_Id Value is 0, and Model value is" that means you are not passeing the values to your Function.

And please use TAGs where you show your code. It is a lot easier to read.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

I would think this should work:

Call UpdateTrainingDates(Me.Bems_ID, Me.AC)

This assumes you have these values in the record source of the form where the code is being run. If you don't know how to set and use a breakpoint now is the time to learn faq705-7148.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Option Explicit

Public Function UpdateTrainingDates(lngBEMS_Id As Long, strModel As String)
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
' the following assumes BEMS_ID is numeric and Model/AC is text
strSQL = "UPDATE tbl_Models_Data SET tbl_Models_Data.[QA Eval Due] = [tbl_Models_Data]![QA-Month Due]," & _
"tbl_Models_Data.StartDateQA = DateAdd('m',-1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & _
"tbl_Models_Data.EndDateQA = DateAdd('m',+1,DateSerial(Year([QA-Month Due]),Month([QA-Month Due]),1)), " & _
"tbl_Models_Data.LastDayQA = DateSerial(Year([QA-Month Due]),Month([QA-Month Due])+2,0) " & _
"WHERE BEMS_Id= " & lngBEMS_Id & " AND Model= """ & strModel & """ "
db.Execute strSQL, dbFailOnError
'MsgBox "Values are: " & Bems_ID & ", and AC value is " & AC
MsgBox "Data updated"
End Function

Here is the Function now...Does it look correct...Note the Option Explicite.

Maybe I have to explain a little more. The dropdown box selects a date and places it in the tables field [QA-Month Due]. Then it calls the code above. The code above takes the new date and modifies the rest of the date fields based on the forumlas. Could we be having a race here...the Call code is running before the[QA-Month Due] field is updated? As an experiment. I updated the Drop Down to the same date twice...thinking maybe the second update...to the same date as the firs would pick up the new date because it was already loaded from the previous selection. This worked. So, how can I delay the Call code from running until the table gets updated from Drop Down? Your thoughts please.

Another quick question...after runing the function does the system go back to the code that does the call? I need to perform a refresh or two specifice to the form I am using....remember I will be calling this from multiple places.

I feel we are almost there and I appreciate ALL of your help.

Thanks,