How To Insert Into a Field With SQL 1

[TimTang]

Hi All!

I've a Port combo box cboPort1 and 2 more cbo boxes called cboArea1 and cboRegion1.

Basically I want cboArea1 and cboRegion1 to fill in automatically after selecting a port.

I've got a SQL query that works on it's own, but not when it's attached to an event on the cboPort1.

This is the code so far:

SELECT tblArea.strArea, tblRegion.strRegion
FROM tblRegion INNER JOIN (tblArea INNER JOIN tblPorts ON tblArea.Area_ID=tblPorts.tblArea_ID) ON tblRegion.Region_ID=tblArea.tblRegion_ID
WHERE (((tblArea!Area_ID)=tblPorts!tblArea_ID) And ((tblRegion!Region_ID)=tblArea!tblRegion_ID) And ((tblPorts.strPort)=forms!frmPositions.cboPort1));

How do I get it to actally insert the results into the cboArea1 and cboRegion1?

Any help would be greatly appreciated.

Cheers

 

[Remou]

Create an Append query.

 

[TimTang]

Hi Remou,

OK! I'll bite; What's an Append query?

Last month I didn't even know what a query was!

Thanks again,

Cheers!!

 

[Remou]

Use the query design screen for now, seeing you are new to this. You will find that Append Query is one of the options on the drop down list of query types. Give it a try and post your SQL back here if you have problems.

 

[TimTang]

Thanks Remou!

I'll give that a try, but first I have to get a cleansing Ale.

It's around 8:00pm in Bangkok right now.

Cheers

 

[TimTang]

Hi Remou,

I still haven't been able to crack this one. I've read all about Append Qrery's but all of the examples provided only use one table, which looks simple and strait forward.

I'm using 4 tables so that seems to confuse the issue.

I know that my code shown above works if I type in a Port "Singapore". It returns a qryTable with the correct area and region. I'm not sure if the last part of my SQL statement is correct because I have no way of knowing what it's returning.

WHERE (((tblArea!Area_ID)=tblPorts!tblArea_ID) And ((tblRegion!Region_ID)=tblArea!tblRegion_ID) And ((tblPorts.strPort)=forms!frmPositions.cboPort1));

Then I have to place the results of this query into cboArea and cboRegion.

I would actually prefer to us the raw SQL because I find the code more understandable than those boxes in the design sceen.

Is there any way to track what's happening in a SQL statement; like know the value of a variable at any stage?

I used to write in lisp and you could follow everything which was very usefull for debugging a program. I think the biggest problem I'm having with SQL is the syntax.

Any idea's would be greatly appreciated.

Cheers!!

 

[randy700]

TimTang,

Put a pause in your code. The easiest way I know to explain how is -- in your code window, click on the gray bar to the left of the code at the point where you want the pause to occur. You'll know you have it if a "dot" appears on the bar.

Now, run your code normally. When it gets to the point where you placed the pause, the code window will open and that line will be hi-lited.

Passing your cursor over any variable will show you it's value. You can also use the Immediate Window. Ctrl-G will open it at the bottom of the screen. To get the value of any variable enter ?VariableName.

To "step through" the code one line at a time, press F8.

You can place multiple pauses in this manner. If you want to let the code run until it reaches the next pause, press F5.


Randy

 

[Remou]

Once you have the SQL from the Append query, you can format it into a string in code, this will allow you to step through as suggested by randy700.

 

[TimTang]

Hi All,

Thanks Randy700 for the tip, that works great and it's very useful to know. It doesn't seem to work for SQL statements though.

I've tried and APPEND, UNION, and INSERT INTO but I still can't get anything into the combo boxes.

I eventually brought the query into vb to see if I could have more success there. I know the query works on it's own and gives the correct output. I just can't for the life of me, insert the answer into the appropriate combo boxes.

Here's my code so far:

Private Sub cboPort1_AfterUpdate()

Dim strSQL As String, db As DAO.Database, rst As DAO.Recordset, strCbo As String

Set db = CurrentDb()
strCbo = Me.cboPort1.Column(1)
strSQL = "SELECT tblArea.strArea, tblRegion.strRegion" & _
            "FROM tblRegion INNER JOIN (tblArea INNER JOIN tblPorts ON tblArea.Area_ID = tblPorts.tblArea_ID)" & _
            "ON tblRegion.Region_ID = tblArea.tblRegion_ID" & _
            "WHERE ((([tblArea]![Area_ID])=[tblPorts]![tblArea_ID]) AND (([tblRegion]![Region_ID])=[tblArea]![tblRegion_ID])" & _
            "AND ((tblPorts.strPort)= ((tblPorts.strPort)=forms!frmPositions.cboPort1));"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

If IsNull(rst(0)) Then
Me.cboArea1 = ""
Else: Me.cboArea1 = rst(0)
End If

If IsNull(rst(1)) Then
Me.cboRegion1 = ""
Else: Me.cboRegion1 = rst(1)
End If

Set rst = Nothing
Set db = Nothing

End Sub

It stops at "Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)". I get "Run-time error 3075" and a message that says "Syntax error (missing operator) in query expression". While the query works on it's own it doesn't like VB.

When I check the value of strSQL it shows the whole expression rather than the results. I don't know if that's what it's supposed to show or not.

Any assistance would be greatly appreciated.

If I can get this to work I would eventually like to change the end of the query from "=forms!frmPositions.cboPort1" to a Variable making the code less specific. I have 12 locations where I'll be using this code so I don't want to re-create the code for each combo box.

The strCbo Variable is redundant; I just wanted to see that the correct port name was being selected.

Cheers!!

 

[TimTang]

Hi All,

Thanks Randy700 for the tip, that works great and it's very useful to know. It doesn't seem to work for SQL statements though.

I've tried and APPEND, UNION, and INSERT INTO but I still can't get anything into the combo boxes. I'm not sure why I should append because the field already exist so they don't have to be appended the just need to be placed in the combo boxes which I can convert to text boxes once this works because the field will be uneditable by the user because they're already set in a different table.

I eventually brought the query into vb to see if I could have more success there. I know the query works on it's own and gives the correct output. I just can't for the life of me, insert the answer into the appropriate combo boxes.

Here's my code so far:

Private Sub cboPort1_AfterUpdate()

Dim strSQL As String, db As DAO.Database, rst As DAO.Recordset, strCbo As String

Set db = CurrentDb()
strCbo = Me.cboPort1.Column(1)
strSQL = "SELECT tblArea.strArea, tblRegion.strRegion" & _
            "FROM tblRegion INNER JOIN (tblArea INNER JOIN tblPorts ON tblArea.Area_ID = tblPorts.tblArea_ID)" & _
            "ON tblRegion.Region_ID = tblArea.tblRegion_ID" & _
            "WHERE ((([tblArea]![Area_ID])=[tblPorts]![tblArea_ID]) AND (([tblRegion]![Region_ID])=[tblArea]![tblRegion_ID])" & _
            "AND ((tblPorts.strPort)= ((tblPorts.strPort)=forms!frmPositions.cboPort1));"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

If IsNull(rst(0)) Then
Me.cboArea1 = ""
Else: Me.cboArea1 = rst(0)
End If

If IsNull(rst(1)) Then
Me.cboRegion1 = ""
Else: Me.cboRegion1 = rst(1)
End If

Set rst = Nothing
Set db = Nothing

End Sub

It stops at "Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)". I get "Run-time error 3075" and a message that says "Syntax error (missing operator) in query expression". While the query works on it's own it doesn't like VB.

When I check the value of strSQL it shows the whole expression rather than the results. I don't know if that's what it's supposed to show or not.

Any assistance would be greatly appreciated.

If I can get this to work I would eventually like to change the end of the query from "=forms!frmPositions.cboPort1" to a Variable making the code less specific. I have 12 locations where I'll be using this code so I don't want to re-create the code for each combo box.

The strCbo Variable is redundant; I just wanted to see that the correct port name was being selected.

Cheers!!

 

[TimTang]

Whooops!!!

Sorry about the double post. I didn't show up so I tried again, about 3 times. I hope another one doesn't show up.

 

[PHV]

what about this ?

strSQL = "SELECT tblArea.strArea, tblRegion.strRegion" & _
            " FROM tblRegion INNER JOIN (tblArea INNER JOIN tblPorts ON tblArea.Area_ID = tblPorts.tblArea_ID)" & _
            " ON tblRegion.Region_ID = tblArea.tblRegion_ID" & _
            " WHERE tblPorts.strPort=forms!frmPositions!cboPort1"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Using the method I outlined, if you pause after assigning a value to strSQL and type ?strSQL in the immediate window, you will see that you are missing spaces every time you continue your line.

For example:

strSQL = "SELECT tblArea.strArea, tblRegion.strRegion" & _
            "FROM tblRegion

will display
SELECT tblArea.strArea, tblRegion.strRegionFROM tblRegion

Randy

 

[TimTang]

Hi randy700

I tried your suggestion but I still got the same error.

I came up with my own solution by leaving the query out of the VB code and just calling it. This seems to resolve any syntax problems. I'm still using the same query I created before because it does produce the proper results. I just don't know how to get it into the text boxes and the table.

Here's my latest:

Private Sub cboPort1_AfterUpdate()

Dim strCbo As Variant
Dim qdf As QueryDef
Dim db As DAO.Database
Dim rst As DAO.Recordset

strCbo = Nz(Me.cboPort1.Column(1))

If strCbo <> Empty Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryPortAreaRegion")
qdf.Parameters(0) = strCbo
Set rst = qdf.OpenRecordset
Me.txtArea1 = rst!strArea
Me.txtRegion1 = rst!strRegion

' close down objects opened:
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

Else
Me.txtArea1 = Nothing
Me.txtRegion1 = Nothing

End If

End Sub

This solved one problem but created another. I had to change the text boxes to unbound for this to work. Now the table is not updated thus the text boxes don't update unless I enter a new port. If I change records the area and region boxes don't change with it.

I don't know why this is so hard to solve. This is a simple case of cascading text boxes or combo boxes. I've read all the examples I could find on the internet but every one of them uses a single table for the cascading information. I can go throught each example and it works every time. The problem I'm having is that I'm using 3 different cascading tables which I'm trying to input into another. I could use a single table but that would contradict the rules of normalization; I don't want to have "Singapore" and "S.E. Asia" repeated hundreds of times in a single table when they should only be required once.

Maybe I'm approaching this the wrong way? I have a separate form where Port, Area, and Region are entered, and you can't exit the form unless all fields are filled in. In the other 2 forms "Vessel Position" and "Cargoes", I just want to call the "Port" and have "Area" and "Region" both filled in automatically, because they already exist. If anything is spelled wrong at least it will be consistantly wrong when used to compare Vessels with Cargoes.

If the "Port" doesn't exist you are prompted to create it using the "Port Input" form.

Does anyone have a better way to accomplish this?

Any advise would be greatly appreciated.