Advertisement

06.04.2008 at 01:20PM PDT, ID: 23458215
[x]
Attachment Details

updateable recordset

Asked by talonsblade in Microsoft ADP, Microsoft Access Database, Access Forms

Tags: microsoft, office, access 2003, adp, vba/sql

I have an access 2003 adp that links to an sql server 2000  database.  i use some stored proecedure to populate a table with all the customers we had for a specific month.  this table named billingcustomers has a bit field that i am using for a true false, for if they are selected to be billed.  on a form i have a subform that pulls this table into a datasheet view with a checkbox for the bit field.  the parent field has two combo boxes on it.  one is to select the month, named cbx_billdate.  the other one (cbx_samsbox) is to select a parrent corporation for the customer because one parrent corporation could have multiple customer accounts, each one gets billed separately. if i use the billdate combobox then all customers are listed.  if i use the samsbox combobox then only customers linked to that parrent corporation are listed.  on problem i am having is that when you click a checkbox, making it true, you cannot deselct it by clicking on it again.  i have read somewhere it is because the bit field and check box are not compatable because sql uses 1 for true where vba uses -1.  i have also read that there is a work around but i did not understand it.  the other problem i have is that if i use the billdate combo box the recordset is updateable but it has all customers listed.  if i use the samsbox combobox for some reason the recordset is not updateable even though it is pretty much the same process.  for both of them i change the recordsource and then requery the subform.  they actualy use the same exact strings.Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:

Private Sub cbx_BillDate_Click()
Dim boxrs As New ADODB.Recordset
Dim strsql As String
Dim boxCnt As Integer
Dim x As Integer
 
strsql = "exec BillingFilterCreateClosed '" & Me.cbx_BillDate & "'"
DoCmd.RunSQL strsql
strsql = "exec nmpFilterCreateClosed '" & Me.cbx_BillDate & "'"
DoCmd.RunSQL strsql
strsql = "exec BillingCustomerpop"
DoCmd.RunSQL strsql
strsql = "select distinct box as maintactvdsg from BillingCustomers"
boxrs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
'strsql = "exec BillingCustomerpop
'DoCmd.RunSQL strsql
If boxrs.BOF = False And boxrs.EOF = False Then
    
    boxrs.MoveFirst
    boxCnt = Me.cbx_samsBox.ListCount
    x = 0
    Do While x < boxCnt
        Me.cbx_samsBox.RemoveItem (0)
        x = x + 1
    Loop
    boxCnt = boxrs.RecordCount
    Me.cbx_samsBox.AddItem "ALL"
    x = 0
    Do While x < boxCnt
        Me.cbx_samsBox.AddItem boxrs!maintactvdsg
        boxrs.MoveNext
        x = x + 1
    Loop
    Me.cbx_samsBox = Me.cbx_samsBox.ItemData(0)
    If Me.cbx_samsBox = "ALL" Then
        strsql = "SELECT billingcustomers.UIC, billingcustomers.name,   Billingcustomers.selected, Billingcustomers.box from billingcustomers where (username = system_user)"
    Else
        strsql = "SELECT billingcustomers.UIC, billingcustomers.name, Billingcustomers.selected, Billingcustomers.box from billingcustomers " _
           & "WHERE (dbo.billingcustomers.box = '" & Me.cbx_samsBox & "') and (username = system_user)"
    End If
        Me.BillingCustomers_subform.Form.RecordSource = strsql
        Me.BillingCustomers_subform.Requery
End If
End Sub
 
 
 
Private Sub cbx_samsBox_Click()
Dim strsql As String
If Me.cbx_samsBox = "ALL" Then
    strsql = "SELECT distinct billingcustomers.UIC, billingcustomers.name, Billingcustomers.selected, Billingcustomers.box from billingcustomers where (username = system_user)"
Else
    strsql = "SELECT  distinct billingcustomers.UIC, billingcustomers.name, Billingcustomers.selected, Billingcustomers.box from billingcustomers " _
       & "WHERE ((dbo.billingcustomers.box = '" & Me.cbx_samsBox & "') and (username = system_user))"
End If
Me.BillingCustomers_subform.Form.RecordSource = strsql
Me.BillingCustomers_subform.Requery
 
 
End Sub
[+][-]06.04.2008 at 08:49PM PDT, ID: 21716250

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.05.2008 at 05:32AM PDT, ID: 21718569

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.05.2008 at 05:41AM PDT, ID: 21718644

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.05.2008 at 05:50AM PDT, ID: 21718725

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.25.2008 at 06:40PM PDT, ID: 21871179

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]06.25.2008 at 06:45PM PDT, ID: 21871200

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Microsoft ADP, Microsoft Access Database, Access Forms
Tags: microsoft, office, access 2003, adp, vba/sql
Sign Up Now!
Solution Provided By: jimpen
Participating Experts: 3
Solution Grade: A
 
 
[+][-]06.26.2008 at 06:24AM PDT, ID: 21874501

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.26.2008 at 06:32AM PDT, ID: 21874563

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628