Using sql output in VBA

[tim bailey]

Hi,

Sorry to bother you guys with such a newbie question but I have a problem where i need to use a query in VBA to derive some values for use in another query.

What I am trying to do is this:

I want to open a form that checks the values of the user input (company name)against the database. Then i want to take those values into a query to open the form where company id = company id then from a combo box select the postcode for the site to be ammended.

The two tables I am working on are:

tbl_customers
company_id
company_name

and

tbl_customers_sites
company_id
postcode
other fields

I can open the recordset for the check against the database but I am really struggling to run and use the output of the query. This is becoming a time issue for me and really would appreciatte the help

thankyou

tim

 

[CameronM]

Hi Tim,

I do a similar thing all the rime, where users select a customer, based on a number of search fields (such as customer name). They can then open a form for a selected customer, which contains a number of 'sites' where the customer is operating from. I use a subform to list the available sites for the customer and then I have a link on the subform to open a 'Site Details' form, which contains the address information about the site.

The basic setup requires that you have the related primary key fields available on each form, but apart from that it is fairly simple

Form1: frmCompanyList (use search fields to filter)
Then use the Onclick event for a button or text field.

DoCmd.OpenForm "frmCompanyDetails", , , "Company_ID=" & txtCompany_ID

Form2: frmCompanyDetails
Have a subform on frmCompanyDetails displaying available sites for this customer, then on the subform use a button or text field to link to the site details form.

DoCmd.OpenForm "frmSiteDetails", , , "Site_ID=" & txtSite_ID

Form3: frmSiteDetails
Contains all the fields required for creating an amending site address values, plus a field used to select the company each site refers to. You can use code on frmCompanyDetails to add a site

    DoCmd.OpenForm "frmSiteDetails"
    DoCmd.GoToRecord acDataForm, "frmSiteDetails", acNewRec
    Forms!frmSiteDetails.cboCompany_ID = Me.Parent.txtCompany_ID

Hope this helps

 

[tim bailey]

Thanks for this, however I am struggling to understand your method.

I have since created a single unbound form on this form i have 2 combo boxes.
the first populates using a query

Private Sub Form_Load()
Dim id As String
Dim cname As String

Cbocname = "select [company name] as cname, [company_id] as cid from tbl_customer"


End Sub

the second uses values from the first to run a query that should then populate the combo when a button is pressed. This is the code:

Private Sub Frec_Click()
On Error GoTo Err_Frec_Click

If Cbocname > 0 Then

Combo4 = "select post_code as postcode from tbl_customer_sites where company_id = '" & Cbocname & "'"

End If

Exit_Frec_Click:
Exit Sub
Err_Frec_Click:
MsgBox Err.Description
Resume Exit_Frec_Click

End Sub

This is the code that is not working. The tables are related in this way
tbl_customer
tbl_customer_sites
tbl_SN_Range

my primary keys are customer_id
Composite key for customer site
customer_id and postcode

tbl_SN_Range is a child to both tbl_customer_sites and tbl_number_carrier.

What I am trying to achieve is I want the user to be able to select a comapny name from a combo box that returns the company id. This can then be used to populate another combo box to which the user can select from the site postcodes available to that customer.

I can then use the DoCmd.Openform to open a form to enter or edit information relating to the customers site number ranges. I appreciatte you earlier attempt to enlighten me and I can see how this works but I am struggling to implement it.

again thankyou

tim

 

[tim bailey]

Ok,

I am making some headway at last and then wham another brick wall.


Please help!!

I have created a form that takes its values from a standard sql select statement in VBA. That works. I then created a second combo box that in turn uses another sql statement from an on click event. This is where I am hitting the wall. The Sql syntax is correct and runs in access but it returns an error of " Item Not Found in This Collection " when run in VBA.

here is the code:

Private Sub Frec_Click()
On Error GoTo Err_Frec_Click
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryPcode")

If Cbocname > 0 Then
id = CInt(Cbocname)
SQL = "select tbl_customer_sites.post_code as postcode from tbl_customer_sites where tbl_customer_sites.company_id =" & id & ""
qdf.SQL = strSQL
DoCmd.OpenQuery "qryPcode"

Combo4 = qryPcode

End If
Debug.Print id
Debug.Print strSQL

Exit_Frec_Click:
Exit Sub

Where am I going wrong, all help appreciatted.

tim

 

[jal]

You're right, the query looks fine. The problem is likely here:

Combo4 = qryPcode

Apparently you want to get the RESULT of the query and make it the selected value of the cbo? The usual method of getting a result is to use DLookup - in fact you probably don't even need a qDef to use DLookup. Here's a tutorial on it.


DLOOKUP looks up one value in a desired table and therefore is a SELECT that returns a single value - you will therefore neeed to provide a WHERE clause geared to return a single value.
SELECT FirstName FROM Customers WHERE CustID = 55
The format is this (note you can lookup a value in a query-output, not just lookup a value in a table).
Dlookup(ColumnName, TableName or QueryName, WHERE clause)
and thus the original query
SELECT FirstName FROM Customers WHERE CustID = 55
translates into this:
Dlookup("FirstName", "Customers", "CustID=55")
be sure to use quotes for strings
SELECT FirstName FROM Customers WHERE LastName LIKE 'Smith'
which is
Dlookup("FirstName", "Customers", "LastName LIKE 'Smith' ")
Again, the WHERE clause must be set up as to return a single value from the column ColumnName. If the WHERE clause is a bad one that returns zero records, the result is Null, which you can detect like this:
Dim var1 As Variant
var1 = DLookup("StudentID", "Students", "Name LIKE 'John' ")
If IsNull(var1) Then MsgBox ("null")

Then you can probably get away with

combo4 = var1

 

[tim bailey]

Thanks for that Unfortunately some of my customers have several sites as such i need to be able to list them hence the recordset approach.

However it has helped my way of thinking. If you have any more ideas they will be appreciatted. I have been working on this for three days solid now with little progress.

tim

 

[jal]

I don't understand - where in your code are you using a recordset? And where did I tell you to stop using recordsets?

And where is the problem, if not the line I mentioned? And if that is the correct line of code, what is wrong with my solution?

 

[tim bailey]

Jal,

Sorry for the confusion. Your dlookup worked fine but was only returning one result. I may of misunderstood your solution and if so let me apologise.

I require the result as you said to populate a combo box but with multiple values. So I had a play and using the VBA help managed to cobble the code below together. However I must be doing something wrong because it still only displays 1 result. my thinking is that it is to do with the movefirst in the recordset so i am going to try to set it to movelast

any ideas? and thankyou again for your help

tim

Private Sub Frec_Click()
On Error GoTo Err_Frec_Click
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As Recordset
Dim strSQL As String
Dim intrecs As Integer

Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
id = CInt(Cbocname)

With qdf
.SQL = "select tbl_customer_sites.post_code as postcode from tbl_customer_sites where tbl_customer_sites.company_id =" & id & ""
Set rs = CurrentDb.OpenRecordset(qdf.SQL)
rs.MoveFirst
End With

With rs
Do While Not .EOF
Combo4 = rs.Fields(0).Value
.MoveFirst
Loop
End With
intrecs = rs.RecordCount
rs.MoveLast

Debug.Print rs

Exit_Frec_Click:
Exit Sub
Err_Frec_Click:
MsgBox Err.Description
Resume Exit_Frec_Click

End Sub

I don't understand - where in your code are you using a recordset? And where did I tell you to stop using recordsets?

And where is the problem, if not the line I mentioned? And if that is the correct line of code, what is wrong with my solution?

 

[jal]

I thought combo4 was already populated and you were trying to select a particular value by a query. Now it seems to me that your query returns multiple values that will populate the cbo. In that case you can use this:

combo4.RowSource = "select tbl_customer_sites.post_code as postcode from tbl_customer_sites where tbl_customer_sites.company_id =" & id

I

 

[tim bailey]

Jal,

Thankyou that worked a treat. You are a lifesaver


tim

I thought combo4 was already populated and you were trying to select a particular value by a query. Now it seems to me that your query returns multiple values that will populate the cbo. In that case you can use this:

combo4.RowSource = "select tbl_customer_sites.post_code as postcode from tbl_customer_sites where tbl_customer_sites.company_id =" & id

I

 

[jal]

Here's another option:

Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "select * from customers", CurrentProject.connection, adOpenStatic, adLockReadOnly
Set combo4.Recordset = rs

 

[CameronM]

Tim,

I am glad jal was able to help you out. For future reference I have attached a very simple example of 2 linked comboboxes, where the second box filters records based on the value of the first.

The key to the whole issue is basically to set your AfterUpdate event for the first combo, to requery the second combo.

Hope this may help in the future.