Query with value from separate forms

[Guest]

The following is the SQL of the query. I am trying to get it to delete the
record depending on whichever form is up. It works but it also asks for the
other records number from the closed form. How can I get this to stop
happening?

DELETE [Error Entry].Index
FROM [Error Entry]
WHERE ((([Error Entry].Index)=[Forms]![F: Off-line Entry]![Index] Or ([Error
Entry].Index)=[Forms]![F: Error Entry]![Index]));

 

[John Spencer (MVP)]

You would need to use some vba to do this. Create a function that checks which
form is open and passes back the value you need.

Where [Error Entry].Index = YourFunctionThatChecks()

Post back if you need help writing the function (or post in the modules section).

 

[Guest]

Yes I need help with this.

I created a Module named MOD:Form Loaded
it has code
'****** Code Start ********
Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function
'****** Code End ********

How do I get the query to reference this code using the below query?
Thanks for the help so far.

You would need to use some vba to do this. Create a function that checks which
form is open and passes back the value you need.

Where [Error Entry].Index = YourFunctionThatChecks()

Post back if you need help writing the function (or post in the modules section).

The following is the SQL of the query. I am trying to get it to delete the
record depending on whichever form is up. It works but it also asks for the
other records number from the closed form. How can I get this to stop
happening?

DELETE [Error Entry].Index
FROM [Error Entry]
WHERE ((([Error Entry].Index)=[Forms]![F: Off-line Entry]![Index] Or ([Error
Entry].Index)=[Forms]![F: Error Entry]![Index]));

 

[John Spencer (MVP)]

You need something more like. I am assuming that your code fIsLoaded works.
*************** UNTESTED AIRCODE ************************
Function fGetMyValue()
If fIsLoaded("[F: Off-line Entry]") = True then
fGetMyValue = [Forms]![F: Off-line Entry]![Index]

ElseIF fIsLoaded("[F: Error Entry]") = True Then
fGetMyValue=[Forms]![F: Error Entry]![Index]
End If

End Function

Now in the query you can use that
DELETE [Error Entry].Index
FROM [Error Entry]
WHERE [Error Entry].Index=fGetMyValue()

Yes I need help with this.

I created a Module named MOD:Form Loaded
it has code
'****** Code Start ********
Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function
'****** Code End ********

How do I get the query to reference this code using the below query?
Thanks for the help so far.

You would need to use some vba to do this. Create a function that checks which
form is open and passes back the value you need.

Where [Error Entry].Index = YourFunctionThatChecks()

Post back if you need help writing the function (or post in the modules section).

The following is the SQL of the query. I am trying to get it to delete the
record depending on whichever form is up. It works but it also asks for the
other records number from the closed form. How can I get this to stop
happening?

DELETE [Error Entry].Index
FROM [Error Entry]
WHERE ((([Error Entry].Index)=[Forms]![F: Off-line Entry]![Index] Or ([Error
Entry].Index)=[Forms]![F: Error Entry]![Index]));

 

[Guest]

Stupid question that comes from inexperience. How do I test a Module to see
if it works? It does not seem to work yet.

You need something more like. I am assuming that your code fIsLoaded works.
*************** UNTESTED AIRCODE ************************
Function fGetMyValue()
If fIsLoaded("[F: Off-line Entry]") = True then
fGetMyValue = [Forms]![F: Off-line Entry]![Index]

ElseIF fIsLoaded("[F: Error Entry]") = True Then
fGetMyValue=[Forms]![F: Error Entry]![Index]
End If

End Function

Now in the query you can use that
DELETE [Error Entry].Index
FROM [Error Entry]
WHERE [Error Entry].Index=fGetMyValue()

Yes I need help with this.

I created a Module named MOD:Form Loaded
it has code
'****** Code Start ********
Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function
'****** Code End ********

How do I get the query to reference this code using the below query?
Thanks for the help so far.

You would need to use some vba to do this. Create a function that checks which
form is open and passes back the value you need.

Where [Error Entry].Index = YourFunctionThatChecks()

Post back if you need help writing the function (or post in the modules section).

James Kendall wrote:

The following is the SQL of the query. I am trying to get it to delete the
record depending on whichever form is up. It works but it also asks for the
other records number from the closed form. How can I get this to stop
happening?

DELETE [Error Entry].Index
FROM [Error Entry]
WHERE ((([Error Entry].Index)=[Forms]![F: Off-line Entry]![Index] Or ([Error
Entry].Index)=[Forms]![F: Error Entry]![Index]));