To open a form, sub form if text box entry exist in Query

[george.vniekerk]

PLease help!!!!!!!



I have an unbound textbox (name=AccLink (used as a Supplier Key)), which derives off the concatenation of two other unbound Text Boxes i.e. Company and Supplier via the following code in the AfterUpdate Event:

Private Sub Supplier_AfterUpdate()
Me.AccLink.Value = Me.Co.Value & Me.Supplier.Value
End Sub

This is done on a "parameter" Form, which if the AccLink/ Supplier Key exists, opens a Main and Sub Form. I have used a Dlookup in the control source of another unbound textbox, to give me a message on the "parameter" Form if the AccLink/ SupplierKey does not exist. This will prompt me to enter a valid Company code and or Supplier Code. The above steps are working perfectly.

On the same form, i have an "Open button" with code in the "on click" Event, that does the following (see code below):

• Prompts me to enter a company code if the Company Text Box is empty.
• Prompts me to enter a Supplier Code if the Supplier Text Box is empty.
• The first two steps above prevents me from opening the Main and Sub Form if the mentioned fields are empty. This is working fine.
• Prompts me to enter a valid Acclink/ Supplier Key if not found in the following Query:

QryConcatCoSupplier.

This is where i am not able to get the desired result as it gives me the the "SupplierKey does not exist" message, irrespective of entering a valid Acclink/ SupplierKey or not (see code below).

• Prompts me that the "SupplierKey does not exist" if it does not exist in the QryConcatCoSupplier Query.
• Uses the AccLink/ Supplier Key (if it exists in the QryConcatCoSupplier Query) in the "parameter" Form to link the Main Form and Sub Forms.

What it should do is proceed to the Main form and Sub Form and link the criteria based on the AccLink/ Supplier Key text box, else give you a message that it doesn't exit.

You will note that i have commented out the section that should do the test of whether it exists in the QryConcatCoSupplier query. If commented out, it works perfectly i.e. opens the Main and Sub Forms and links the forms based on the AccLink text box criteria.

Problem is, if the AccLink/ Supplier Key text box does not exist, it still opens the Main and Sub Forms, however it is blank.

I should not be able to open the Main and Sub Forms if the AccLink/ Supplier Key does not exist in the QryConcatCoSupplier Query.


Private Sub OpenSupplier_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FrmAddAPRecons"

stLinkCriteria = "[AccLink]=" & "'" & Me![AccLink] & "'"

If IsNull(Me.Co) Then
MsgBox "You must enter a Company Code'"
DoCmd.GoToControl "Co"
Exit Sub

Else
If IsNull(Me.Supplier) Then
MsgBox "You must enter a Supplier Code"
DoCmd.GoToControl "Supplier"
Exit Sub

End If

'If Me.AccLink.Value = DLookup("[AccLink]", "[QryConcatCoSupplier]", "[AccLink] = '" & Me.AccLink.Value & "'") Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name
'Else
'MsgBox "SupplierKey does not exist"
Exit_OpenSupplier_Click:
Exit Sub
Err_OpenSupplier_Click:
MsgBox Err.Description
Resume Exit_OpenSupplier_Click
'End If
End If
End Sub

[boblarson]

How about

If DCount("*", "QryConcatCoSupplier", stLinkCriteria) > 0 Then
   DoCmd.OpenForm stDocName WhereCondition:=stLinkCriteria

[george.vniekerk]

THANK YOU boblarson!!!!!!!! You are truly an Access superstar in my books, especially because of the fact that you were the only response to my post out of the many views. I was genuinely concerned that i would not get a solutun here. Thanks again!