DoCmd.OpenForm with a WHERE clause as SQL?

[Federico]

Help much appreciated:

1. Each record has a unique [GlobalID] in the Samples table.
2. Each GlobalID is associated with several [AliquotBC] in the Aliquots table

Samples Aliquots
---------- ---------
GlobalID <-> GlobalID
AliquotBC

From an unbound form, I need to be able to open and FILTER the Samples form
based on an AliquotBC that the user enters. So far, the form opens but does
not filter the record (GlobalID) based on the AliquotBC:

(...from a select case menu option)

If (IsNull(Me!AliquotBCMethod)) Then
DoCmd.Beep
MsgBox "Please Enter the Aliquot Barcode", vbOKOnly, "Ooops!"
Exit Sub
Else
AliquotBCSelected = Me.AliquotBCMethod
Set dbs = CurrentDb
strSQL = "SELECT Aliquots.AliquotBC FROM Samples INNER JOIN Aliquots
ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC=" & AliquotBCSelected
& ";"
Set rst = dbs.OpenRecordset(strSQL)

If rst.EOF Then
MsgBox "The Aliquot Barcode entered does not exist in StoneBase."
Me.AliquotBCMethod.SetFocus
Exit Sub
Else
'Opens Samples Form
dbs.Close
Set rst = Nothing

'Finds the GlobalID based on the AliquotBC via a new query
strLinkCriteria = "'[GlobalID]= Samples.GlobalID FROM Samples INNER
JOIN Aliquots ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC=" &
AliquotBCSelected & ";'"

DoCmd.Close 'closes FindSamples form
DoCmd.OpenForm "Samples", acNormal, , strLinkCriteria

End If
End If

 

[Douglas J. Steele]

Assuming that the RecordSource of the form is a query that joins Samples and
Aliquots, all you should need is

strLinkCriteria = "AliquotBC=" & AliquotBCSelected

 

[Federico]

Dear Doug,

Worked! Thanks for the post. The Samples form was based on the Samples
table, so I assigned the RecordSource to a qrySamples that includes the
AliquotBC.

The problem that I have now when opening the Samples Form is that it has
multiplied the records to include as many entries as Aliquots are entered
(pulled from the second table). I'm sure that should be an easy fix... issue
with relationships? Distinctrow did not help... Will appreciate any thoughts.

Federico

Assuming that the RecordSource of the form is a query that joins Samples and
Aliquots, all you should need is

strLinkCriteria = "AliquotBC=" & AliquotBCSelected

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Help much appreciated:

1. Each record has a unique [GlobalID] in the Samples table.
2. Each GlobalID is associated with several [AliquotBC] in the Aliquots
table

Samples Aliquots
---------- ---------
GlobalID <-> GlobalID
AliquotBC

From an unbound form, I need to be able to open and FILTER the Samples
form
based on an AliquotBC that the user enters. So far, the form opens but
does
not filter the record (GlobalID) based on the AliquotBC:

(...from a select case menu option)

If (IsNull(Me!AliquotBCMethod)) Then
DoCmd.Beep
MsgBox "Please Enter the Aliquot Barcode", vbOKOnly, "Ooops!"
Exit Sub
Else
AliquotBCSelected = Me.AliquotBCMethod
Set dbs = CurrentDb
strSQL = "SELECT Aliquots.AliquotBC FROM Samples INNER JOIN
Aliquots
ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC=" &
AliquotBCSelected
& ";"
Set rst = dbs.OpenRecordset(strSQL)

If rst.EOF Then
MsgBox "The Aliquot Barcode entered does not exist in
StoneBase."
Me.AliquotBCMethod.SetFocus
Exit Sub
Else
'Opens Samples Form
dbs.Close
Set rst = Nothing

'Finds the GlobalID based on the AliquotBC via a new query
strLinkCriteria = "'[GlobalID]= Samples.GlobalID FROM Samples INNER
JOIN Aliquots ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC=" &
AliquotBCSelected & ";'"

DoCmd.Close 'closes FindSamples form
DoCmd.OpenForm "Samples", acNormal, , strLinkCriteria

End If
End If

 

[Douglas J. Steele]

What's the SQL of the query that makes up the RecordSource for the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Doug,

Worked! Thanks for the post. The Samples form was based on the Samples
table, so I assigned the RecordSource to a qrySamples that includes the
AliquotBC.

The problem that I have now when opening the Samples Form is that it has
multiplied the records to include as many entries as Aliquots are entered
(pulled from the second table). I'm sure that should be an easy fix...
issue
with relationships? Distinctrow did not help... Will appreciate any
thoughts.

Federico

Assuming that the RecordSource of the form is a query that joins Samples
and
Aliquots, all you should need is

strLinkCriteria = "AliquotBC=" & AliquotBCSelected

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Help much appreciated:

1. Each record has a unique [GlobalID] in the Samples table.
2. Each GlobalID is associated with several [AliquotBC] in the Aliquots
table

Samples Aliquots
---------- ---------
GlobalID <-> GlobalID
AliquotBC

From an unbound form, I need to be able to open and FILTER the Samples
form
based on an AliquotBC that the user enters. So far, the form opens but
does
not filter the record (GlobalID) based on the AliquotBC:

(...from a select case menu option)

If (IsNull(Me!AliquotBCMethod)) Then
DoCmd.Beep
MsgBox "Please Enter the Aliquot Barcode", vbOKOnly,
"Ooops!"
Exit Sub
Else
AliquotBCSelected = Me.AliquotBCMethod
Set dbs = CurrentDb
strSQL = "SELECT Aliquots.AliquotBC FROM Samples INNER JOIN
Aliquots
ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC=" &
AliquotBCSelected
& ";"
Set rst = dbs.OpenRecordset(strSQL)

If rst.EOF Then
MsgBox "The Aliquot Barcode entered does not exist in
StoneBase."
Me.AliquotBCMethod.SetFocus
Exit Sub
Else
'Opens Samples Form
dbs.Close
Set rst = Nothing

'Finds the GlobalID based on the AliquotBC via a new query
strLinkCriteria = "'[GlobalID]= Samples.GlobalID FROM Samples
INNER
JOIN Aliquots ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC="
&
AliquotBCSelected & ";'"

DoCmd.Close 'closes FindSamples form
DoCmd.OpenForm "Samples", acNormal, , strLinkCriteria

End If
End If

 

[Federico]

------------------
SELECT Samples.*, Aliquots.AliquotBC
FROM Samples INNER JOIN Aliquots ON Samples.GlobalID = Aliquots.GlobalID;
------------------

I've tried LEFT JOIN too without success

What's the SQL of the query that makes up the RecordSource for the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Doug,

Worked! Thanks for the post. The Samples form was based on the Samples
table, so I assigned the RecordSource to a qrySamples that includes the
AliquotBC.

The problem that I have now when opening the Samples Form is that it has
multiplied the records to include as many entries as Aliquots are entered
(pulled from the second table). I'm sure that should be an easy fix...
issue
with relationships? Distinctrow did not help... Will appreciate any
thoughts.

Federico

Assuming that the RecordSource of the form is a query that joins Samples
and
Aliquots, all you should need is

strLinkCriteria = "AliquotBC=" & AliquotBCSelected

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Help much appreciated:

1. Each record has a unique [GlobalID] in the Samples table.
2. Each GlobalID is associated with several [AliquotBC] in the Aliquots
table

Samples Aliquots
---------- ---------
GlobalID <-> GlobalID
AliquotBC

From an unbound form, I need to be able to open and FILTER the Samples
form
based on an AliquotBC that the user enters. So far, the form opens but
does
not filter the record (GlobalID) based on the AliquotBC:

(...from a select case menu option)

If (IsNull(Me!AliquotBCMethod)) Then
DoCmd.Beep
MsgBox "Please Enter the Aliquot Barcode", vbOKOnly,
"Ooops!"
Exit Sub
Else
AliquotBCSelected = Me.AliquotBCMethod
Set dbs = CurrentDb
strSQL = "SELECT Aliquots.AliquotBC FROM Samples INNER JOIN
Aliquots
ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC=" &
AliquotBCSelected
& ";"
Set rst = dbs.OpenRecordset(strSQL)

If rst.EOF Then
MsgBox "The Aliquot Barcode entered does not exist in
StoneBase."
Me.AliquotBCMethod.SetFocus
Exit Sub
Else
'Opens Samples Form
dbs.Close
Set rst = Nothing

'Finds the GlobalID based on the AliquotBC via a new query
strLinkCriteria = "'[GlobalID]= Samples.GlobalID FROM Samples
INNER
JOIN Aliquots ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC="
&
AliquotBCSelected & ";'"

DoCmd.Close 'closes FindSamples form
DoCmd.OpenForm "Samples", acNormal, , strLinkCriteria

End If
End If

 

[Federico]

I was thinking about you mentioning the RecordSource.

Rewriting the code as this provides the purpose (NOTE: I converted AliquotBC
to Text field):

....
Case 5 'by AliquotBC

If IsNull(Me.AliquotBCMethod) = True Then
DoCmd.Beep
MsgBox "Please Enter the Aliquot Barcode", vbOKOnly, "Ooops!"
Exit Sub
Else
AliquotBCSelected = Me.AliquotBCMethod
strLinkCriteria = "AliquotBC= '" & [AliquotBCSelected] & "' "

If IsNull(DLookup("AliquotBC", "qrySamplesAliquots",
strLinkCriteria)) Then
MsgBox "The Aliquot Barcode entered does not exist in StoneBase."
Me.AliquotBCMethod.SetFocus
Exit Sub
End If

strSQL = "SELECT Samples.*, Aliquots.AliquotBC " & _
"FROM Samples INNER JOIN Aliquots ON Samples.GlobalID =
Aliquots.GlobalID " & _
"WHERE Aliquots.AliquotBC=""" & [AliquotBCSelected] & """;"

DoCmd.Close 'closes FindSamples form
DoCmd.OpenForm "Samples", acNormal
Forms!Samples.RecordSource = strSQL

End If
....

What's the SQL of the query that makes up the RecordSource for the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Doug,

Worked! Thanks for the post. The Samples form was based on the Samples
table, so I assigned the RecordSource to a qrySamples that includes the
AliquotBC.

The problem that I have now when opening the Samples Form is that it has
multiplied the records to include as many entries as Aliquots are entered
(pulled from the second table). I'm sure that should be an easy fix...
issue
with relationships? Distinctrow did not help... Will appreciate any
thoughts.

Federico

Assuming that the RecordSource of the form is a query that joins Samples
and
Aliquots, all you should need is

strLinkCriteria = "AliquotBC=" & AliquotBCSelected

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Help much appreciated:

1. Each record has a unique [GlobalID] in the Samples table.
2. Each GlobalID is associated with several [AliquotBC] in the Aliquots
table

Samples Aliquots
---------- ---------
GlobalID <-> GlobalID
AliquotBC

From an unbound form, I need to be able to open and FILTER the Samples
form
based on an AliquotBC that the user enters. So far, the form opens but
does
not filter the record (GlobalID) based on the AliquotBC:

(...from a select case menu option)

If (IsNull(Me!AliquotBCMethod)) Then
DoCmd.Beep
MsgBox "Please Enter the Aliquot Barcode", vbOKOnly,
"Ooops!"
Exit Sub
Else
AliquotBCSelected = Me.AliquotBCMethod
Set dbs = CurrentDb
strSQL = "SELECT Aliquots.AliquotBC FROM Samples INNER JOIN
Aliquots
ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC=" &
AliquotBCSelected
& ";"
Set rst = dbs.OpenRecordset(strSQL)

If rst.EOF Then
MsgBox "The Aliquot Barcode entered does not exist in
StoneBase."
Me.AliquotBCMethod.SetFocus
Exit Sub
Else
'Opens Samples Form
dbs.Close
Set rst = Nothing

'Finds the GlobalID based on the AliquotBC via a new query
strLinkCriteria = "'[GlobalID]= Samples.GlobalID FROM Samples
INNER
JOIN Aliquots ON Samples.GlobalID = Aliquots.GlobalID WHERE AliquotBC="
&
AliquotBCSelected & ";'"

DoCmd.Close 'closes FindSamples form
DoCmd.OpenForm "Samples", acNormal, , strLinkCriteria

End If
End If