Complex Search Form (1 Viewer)

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
Hi all, and ty for your suggestions that's my issue:

I have a form used to show in a subform the results from the selection of two comboboxes.
These are the combobox name and the relative query on the main table "Documents":

Code:
Dim typeSearch As String
    If Me.docType.Value = "EMPTY" Then
          typeSearch = "select * from Documents where ([code] = " & Me.cliCode & ")"
     Else
          typeSearch = "select * from Documents where ([typeDoc]  = '" & Me.docType & "') and ([code] = " & Me.cliCode & ")"
     End If
Me!Submask_docSearch.Form.RecordSource = typeSearch
Me!Submask_docSearch.Form.Requery

- docType (the DOCUMENT TYPE)
Code:
Dim typeSearch As String
    If Me.cliCode.Value = "EMPTY" Then
          typeSearch = "select * from Documents where ([typeDoc] = " & Me.docType & ")"
     Else
          typeSearch = "select * from Documents where ([typeDoc]  = '" & Me.docType & "') and ([code] = " & Me.cliCode & ")"
     End If
Me!Submask_docSearch.Form.RecordSource = typeSearch
Me!Submask_docSearch.Form.Requery

This vba start the query based on the comboboxes.
(tell me if is the right way to perform that).

Now, the issue is that i want to upgrade that SEARCH with another combobox pointing another table:
the new table "RESULTS" has three fields:
- ID (linked with the resID of "DOCUMENTS")
- DOC CODE (one of the code in Documents)
- RESULT (APPROVED, REJECTED)
This table is joint with the "DOCUMENTS" table through a field resID.
When i choose APPROVED, the search must find ALL the documents inside the Documents table that has the type of result linked with the RESULT table with the same code.

EXAMPLE:
TABLE DOCUMENTS
Code = 2222
typeDoc = EXAM
resID = 1
-
Code = 2223
typeDoc = EXAM
resID = 2

TABLE RESULTS
ID = 1
DOC CODE = 2222
RESULT = APPROVED
-
ID = 2
DOC CODE = 2223
RESULT = APPROVED

If i select from comboboxes:
EXAMS and APPROVED
It must show me
code(from documents) = 2222, typeDoc(from documents) = EXAM, result(from RESULT) = APPROVED
-
code(from documents) = 2223, typeDoc(from documents) = EXAM, result(from RESULT) = APPROVED


I hope i explained well...if not ask me.
TY!!!!
 
Last edited by a moderator:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:42
Joined
May 7, 2009
Messages
19,175
private sub newCombo_AfterUpdate()
Call subNewRecordSource
End Sub

Private sub cliCode_AfterUpdate()
Call subNewRecordSource
End Sub

Private Sub docType_AfterUpdate()
Call subNewRecordSource
End Sub

Private Sub subNewRecordSource
Dim typeSearch As String
'000
If Me.cliCode.Value = "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents;" : Exit Sub

'001
IF If Me.cliCode.Value = "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents WHERE [ID] = " & Me.newCombo.Value & ";" : Exit Sub

'010
IF If Me.cliCode.Value = "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents where [typeDoc] = " & Chr(34) & Me.docType & Chr(34) & ";" : Exit Sub

'011
IF If Me.cliCode.Value = "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents where [typeDoc] = " & Chr(34) & Me.docType & Chr(34) & " And [ID] = " & Me.newCombo.Value & ";" : Exit Sub

'100
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents where
Code:
 = " & Chr(34) & Me.cliCode & Chr(34) & ";" : Exit Sub

'101
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [ID] = " & Me.newCombo.Value & ";" : Exit Sub

'110
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [typedoc] = " & Chr(34) & Me.docType & Chr(34) & ";" : Exit Sub
'111
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [typedoc] = " & Chr(34) & Me.docType & Chr(34) & _
" And [ID] = Me.newCombo & ";" 

Me!Submask_docSearch.Form.RecordSource = typeSearch
Me!Submask_docSearch.Form.Requery
End Sub
 

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
private sub newCombo_AfterUpdate()
Call subNewRecordSource
End Sub

Private sub cliCode_AfterUpdate()
Call subNewRecordSource
End Sub

Private Sub docType_AfterUpdate()
Call subNewRecordSource
End Sub

Private Sub subNewRecordSource
Dim typeSearch As String
'000
If Me.cliCode.Value = "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents;" : Exit Sub

'001
IF If Me.cliCode.Value = "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents WHERE [ID] = " & Me.newCombo.Value & ";" : Exit Sub

'010
IF If Me.cliCode.Value = "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents where [typeDoc] = " & Chr(34) & Me.docType & Chr(34) & ";" : Exit Sub

'011
IF If Me.cliCode.Value = "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents where [typeDoc] = " & Chr(34) & Me.docType & Chr(34) & " And [ID] = " & Me.newCombo.Value & ";" : Exit Sub

'100
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents where
Code:
 = " & Chr(34) & Me.cliCode & Chr(34) & ";" : Exit Sub

'101
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [ID] = " & Me.newCombo.Value & ";" : Exit Sub

'110
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [typedoc] = " & Chr(34) & Me.docType & Chr(34) & ";" : Exit Sub
'111
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [typedoc] = " & Chr(34) & Me.docType & Chr(34) & _
" And [ID] = Me.newCombo & ";" 

Me!Submask_docSearch.Form.RecordSource = typeSearch
Me!Submask_docSearch.Form.Requery
End Sub[/QUOTE]

TY but 2 things:
1) why the SELECT are always on DOCUMENTS TABLE and never on RESULT TABLE?
2) I have a "compilation error" at the end, before Me!....
 

Cronk

Registered User.
Local time
Today, 23:42
Joined
Jul 4, 2013
Messages
2,770
The code is missing corresponding
End If's
 

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
I got on the line:
typeSearch = "select * from Documents where
Code:
 = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [typedoc] = " & Chr(34) & Me.docType & Chr(34) & _
" And [ID] = Me.newCombo & ";" 


ERROR:
End instruction required ";"


But i still can't understand the code.....
why use chr(34) instead of "?
what do that line of code:
"select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & " And [typedoc] = " & Chr(34) & Me.docType & Chr(34) & " And [ID] = Me.newCombo & ";" ????

Ty

Have you got other suggestiones?
 

Cronk

Registered User.
Local time
Today, 23:42
Joined
Jul 4, 2013
Messages
2,770
If you were to type in the Immediate window
? chr(34)
you'd get
""

So chr(34) wraps required quotes around your string value, ie the line evaluates as, say

select * from Documents where
Code:
 = "EMPTY" And .....
 

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
Problem creating the query string select:

typeSearch = "select * from mainTable where ([Status] = '" & Me.NewComboBox & "')"


Me!Submask_mainTable.Form.RecordSource = typeSearch
Me!Sottomaschera_mainTable.Form.Requery


The newcombobox, contain a text.

When i execute the select, it ask me the [Status] VALUE.
 

Mile-O

Back once again...
Local time
Today, 12:42
Joined
Dec 10, 2002
Messages
11,316
Apologies, I tried to add adding [CODE] [/CODE] tags to the original post, but the code having a field called [CODE] threw it off.
 

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
Apologies, I tried to add adding [CODE] [/CODE] tags to the original post, but the code having a field called [CODE] threw it off.

...sorry but i can't understand what are you talking about....
Anyway....
A suggestion?
 

Mile-O

Back once again...
Local time
Today, 12:42
Joined
Dec 10, 2002
Messages
11,316
...sorry but i can't understand what are you talking about....

I added tags so that some code doesn't look like this:

Dim intExample As Integer

For intExample = 1 To 10
MsgBox "Example " & intExample
Next intExample

But instead looks like this.

Code:
Dim intExample As Integer

For intExample = 1 To 10
    MsgBox "Example " & intExample
Next intExample

which we get by encapsulating the code in [CODE] [/CODE] tags like so:

[CODE]
Dim intExample As Integer

For intExample = 1 To 10
MsgBox "Example " & intExample
Next intExample
[/CODE]

It just makes reading code easier.
 

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
I added tags so that some code doesn't look like this:

Dim intExample As Integer

For intExample = 1 To 10
MsgBox "Example " & intExample
Next intExample

But instead looks like this.

Code:
Dim intExample As Integer

For intExample = 1 To 10
    MsgBox "Example " & intExample
Next intExample

which we get by encapsulating the code in [CODE] [/CODE] tags like so:

[CODE]
Dim intExample As Integer

For intExample = 1 To 10
MsgBox "Example " & intExample
Next intExample
[/CODE]

It just makes reading code easier.

Understood. TY
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:42
Joined
May 7, 2009
Messages
19,175
private sub newCombo_AfterUpdate()
Call subNewRecordSource
End Sub

Private sub cliCode_AfterUpdate()
Call subNewRecordSource
End Sub

Private Sub docType_AfterUpdate()
Call subNewRecordSource
End Sub

Private Sub subNewRecordSource
Dim typeSearch As String
'000
If Me.cliCode.Value = "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents;" : Goto Graceful_Exit

'001
IF If Me.cliCode.Value = "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents WHERE [ID] = " & Me.newCombo.Value & ";" : Goto Graceful_Exit

'010
IF If Me.cliCode.Value = "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents where [typeDoc] = " & Chr(34) & Me.docType & Chr(34) & ";" : Goto Graceful_Exit

'011
IF If Me.cliCode.Value = "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents where [typeDoc] = " & Chr(34) & Me.docType & Chr(34) & " And [ID] = " & Me.newCombo.Value & ";" : Goto Graceful_Exit

'100
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents where
Code:
 = " & Chr(34) & Me.cliCode & Chr(34) & ";" : Goto Graceful_Exit

'101
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value = "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [ID] = " & Me.newCombo.Value & ";" : Goto Graceful_Exit

'110
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") = 0 Then _
typeSearch = "select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [typedoc] = " & Chr(34) & Me.docType & Chr(34) & ";" : Goto Graceful_Exit
'111
IF If Me.cliCode.Value <> "EMPTY" And Me.docType.Value <> "EMPTY" And _
Val(Me.newCombo.Value & "") <> 0 Then _
typeSearch = "select * from Documents where [code] = " & Chr(34) & Me.cliCode & Chr(34) & _
" And [typedoc] = " & Chr(34) & Me.docType & Chr(34) & _
" And [ID] = Me.newCombo & ";"

Graceful_Exit:
Me!Submask_docSearch.Form.RecordSource = typeSearch
Me!Submask_docSearch.Form.Requery
End Sub
 

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
I am testing that code:
i found DOUBLE IF in each "select generator" and the END IF missed.

But many things not working....

I think i need to rethink about it.
 

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
The problem is:

From 3 combobox criteria, query two connected tables and show the results in a subform.

The combobox are:
1- CLIENT_NAME (char) (default value: Null)
2- TYPE (char) () (default value: "EMPTY")
3- RESULT (char) (default value: "EMPTY")

Tables are:
name: ESTIMATE
fields: ID, CLIENT_NAME, TYPE, ID_RESULT

name: RESULTS
fields: ID, RESULT

I made a join between these two tables:
from ID_RESULT to ID

That's my situation.
Eventually i can unify these two tables.....

TY
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:42
Joined
May 7, 2009
Messages
19,175
you just remove the extra IF on each If statement. there is no need to end it with End If, since this is an inline statement.
 

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
ok, but the search made a strange behavior...
If i try to perform a search with the "001 select":
it shows me the record with the corrected field selected, but with another field with: "#Name?"


Next trying to perform the serch with "001 select":
not showing the results asking me [Field] value of the comboboxes.


......i think there is a problem with the 2 tables......
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:42
Joined
May 7, 2009
Messages
19,175
can you show us the resulting query statement when you linked the two table.
 

michi.for

Registered User.
Local time
Today, 05:42
Joined
Jan 13, 2016
Messages
45
Code:
select estimate.id, estimate.client_name, estimate.result, results.id, results.result
from results inner join estimate on results.id = estimate.result;
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:42
Joined
May 7, 2009
Messages
19,175
select estimate.id, estimate.client_name, estimate.result, results.id, results.result
from results inner join estimate on results.id = estimate.id_result;

you have two ID's returning from your query, so #Name is coming to your form, you should rename the other, ie:

results.id AS result_id
 
Last edited:

Users who are viewing this thread

Top Bottom