Help with code

[short]

I'm creating a database that when the user selects the item in the combo box
and clicks the command button the information about that certain item is
displayed in their tables...

This is my code for where I'm have troubles:

ElseIf cbChangeView.value = "Application" Then
'///////////////////////////////////////////
'SetDefaults() = populates the rest of the table
'Table# = Name of the displayed table
Call SetDefaults(1, "Application", True)
Table1 = "Table.Application"
If IsNull(Me.cbName.value) Then
Me.cbName.value = ""
End If

'Sets the query statements for all of the tables used
Query1 = "SELECT * FROM Application WHERE Application.[Name]='" +
cbName.value + "';"
Query2 = "SELECT JVM.* FROM JVM, ApplicationJVM, Application WHERE " _
& "Application.[ID] = ApplicationJVM.[AppID] AND
ApplicationJVM.[JVMID]" _
& "= JVM.[ID] AND Application.[Name]='" + cbName.value + "';"
Query3 = "SELECT * FROM ApplicationJVM,Application WHERE Application.[ID]" _
& "= ApplicationJVM.[AppID] AND Application.[Name]='" +
cbName.value + "';"
Query4 = "SELECT Cluster.* FROM Cluster INNER JOIN (Application INNER JOIN
ApplicationCluster ON Application.ID = ApplicationCluster.AppID) ON
Cluster.ID = ApplicationCluster.CID WHERE " _
& " (((Application.ID)=[Application].[ID])) AND Application.[Name] = '" +
cbName.value + "';"

Query4 is what is not working, nothing is properly displayed. For query4
it's connected to the tables cluster, application, and applicationcluster.
Applicationcluster holds the primary keys for both the cluster and
application and connects them if that makes sense.

 

[short]

sorry this is what query4 says:

Query4 = "SELECT Cluster.* FROM Cluster INNER JOIN (Application INNER JOIN
ApplicationCluster ON Application.ID = ApplicationCluster.AppID) ON
Cluster.ID = ApplicationCluster.CID WHERE " _
& " (((Application.ID)=[Application].[ID])) AND Application.[Name] = '" +
cbName.value + "';"

 

[Paolo]

Hi short,
I don't understand the meaning of this part of query4

WHERE " _
& " (((Application.ID)=[Application].[ID]))

is this correct?

Paolo

sorry this is what query4 says:

Query4 = "SELECT Cluster.* FROM Cluster INNER JOIN (Application INNER JOIN
ApplicationCluster ON Application.ID = ApplicationCluster.AppID) ON
Cluster.ID = ApplicationCluster.CID WHERE " _
& " (((Application.ID)=[Application].[ID])) AND Application.[Name] = '" +
cbName.value + "';"

I'm creating a database that when the user selects the item in the combo box
and clicks the command button the information about that certain item is
displayed in their tables...

This is my code for where I'm have troubles:

ElseIf cbChangeView.value = "Application" Then
'///////////////////////////////////////////
'SetDefaults() = populates the rest of the table
'Table# = Name of the displayed table
Call SetDefaults(1, "Application", True)
Table1 = "Table.Application"
If IsNull(Me.cbName.value) Then
Me.cbName.value = ""
End If

'Sets the query statements for all of the tables used
Query1 = "SELECT * FROM Application WHERE Application.[Name]='" +
cbName.value + "';"
Query2 = "SELECT JVM.* FROM JVM, ApplicationJVM, Application WHERE " _
& "Application.[ID] = ApplicationJVM.[AppID] AND
ApplicationJVM.[JVMID]" _
& "= JVM.[ID] AND Application.[Name]='" + cbName.value + "';"
Query3 = "SELECT * FROM ApplicationJVM,Application WHERE Application.[ID]" _
& "= ApplicationJVM.[AppID] AND Application.[Name]='" +
cbName.value + "';"
Query4 = "SELECT Cluster.* FROM Cluster INNER JOIN (Application INNER JOIN
ApplicationCluster ON Application.ID = ApplicationCluster.AppID) ON
Cluster.ID = ApplicationCluster.CID WHERE " _
& " (((Application.ID)=[Application].[ID])) AND Application.[Name] = '" +
cbName.value + "';"

Query4 is what is not working, nothing is properly displayed. For query4
it's connected to the tables cluster, application, and applicationcluster.
Applicationcluster holds the primary keys for both the cluster and
application and connects them if that makes sense.

 

[short]

I have now chaged it to:
Query4 = "SELECT Cluster.* FROM Cluster, ApplicationCluster, Application
WHERE Application.[ID] = ApplicationCluster.[AppID] AND
ApplicationCluster.[CID]= Cluster.[ID] AND Application.[Name]= '" +
cbName.value + "';"

it's still not working and I'm thinking it might have to do with the add
form I have..

which is this:

'/////////////////////////////////////////////////////////////////
ElseIf Forms!UpdateInformation.Form!cbChangeView.value = "Application" Then
'/////////////////////////////////////////////////////////////////
'this is the name of the server
name = Forms!UpdateInformation.Form!cbName.value

Set db = CurrentDb

'this gets the server table ID
strSQL = "SELECT * FROM Application WHERE " + "Application.[Name]='" +
name + "';"
Set rs2 = db.OpenRecordset(strSQL)
'This is the server ID
If rs2.RecordCount > 0 Then
intResult = rs2("ID")
Else
intResult = 0
End If
'this gets everything in the table and sets them to a record set
Set cn = CurrentProject.Connection
Set rs = cn.Execute("Select * From " + Me.lbOptionTitle.Caption)

'sets everything on the form to blank
Call CleanUpItems

'populates the form with the values
For i = 0 To rs.Fields.Count - 1 Step 1
'gets the field name
name = rs.Fields(i).name + ""
'makes the field name visible
Call FillInItems(i, name, True)


If i < 4 Then
'check to see if there should be a lookup button
Call FindIDs(name, i)
'search "name" == "_ID_Table"
' if a name matches the id table then
' hide the text box, and make the button visible
End If

Next i



'Searches for a foreign key and auto populates it
Call FKpop("Application.ID", intResult)
Call FKpop("CID", intResult)

Hi short,
I don't understand the meaning of this part of query4

WHERE " _
& " (((Application.ID)=[Application].[ID]))

is this correct?

Paolo

sorry this is what query4 says:

Query4 = "SELECT Cluster.* FROM Cluster INNER JOIN (Application INNER JOIN
ApplicationCluster ON Application.ID = ApplicationCluster.AppID) ON
Cluster.ID = ApplicationCluster.CID WHERE " _
& " (((Application.ID)=[Application].[ID])) AND Application.[Name] = '" +
cbName.value + "';"

I'm creating a database that when the user selects the item in the combo box
and clicks the command button the information about that certain item is
displayed in their tables...

This is my code for where I'm have troubles:

ElseIf cbChangeView.value = "Application" Then
'///////////////////////////////////////////
'SetDefaults() = populates the rest of the table
'Table# = Name of the displayed table
Call SetDefaults(1, "Application", True)
Table1 = "Table.Application"
If IsNull(Me.cbName.value) Then
Me.cbName.value = ""
End If

'Sets the query statements for all of the tables used
Query1 = "SELECT * FROM Application WHERE Application.[Name]='" +
cbName.value + "';"
Query2 = "SELECT JVM.* FROM JVM, ApplicationJVM, Application WHERE " _
& "Application.[ID] = ApplicationJVM.[AppID] AND
ApplicationJVM.[JVMID]" _
& "= JVM.[ID] AND Application.[Name]='" + cbName.value + "';"
Query3 = "SELECT * FROM ApplicationJVM,Application WHERE Application.[ID]" _
& "= ApplicationJVM.[AppID] AND Application.[Name]='" +
cbName.value + "';"
Query4 = "SELECT Cluster.* FROM Cluster INNER JOIN (Application INNER JOIN
ApplicationCluster ON Application.ID = ApplicationCluster.AppID) ON
Cluster.ID = ApplicationCluster.CID WHERE " _
& " (((Application.ID)=[Application].[ID])) AND Application.[Name] = '" +
cbName.value + "';"

Query4 is what is not working, nothing is properly displayed. For query4
it's connected to the tables cluster, application, and applicationcluster.
Applicationcluster holds the primary keys for both the cluster and
application and connects them if that makes sense.