October 13, 2008 11:29pm pdt

My Account

Top 15 Experts

Overall Top Experts

1LSMCons... 99,925
2vadimrapp1 91,600
3LPurvis 89,936
4peter57r 80,586
5rockiroads 44,447
6capricorn1 39,518
7SidFishes 37,544
8jimhorn 36,229
9stevbe 31,815
10nico5038 26,250
11thenelson 24,639
12angelIII 22,577
13JDettman 20,668
14boag2000 20,665
15matthews... 20,125

	[x] Attachment Details

updateable recordset

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.8

Zones: 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.

         
           
             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
           
         

	#	Title
	1	Recordset Not Updatable
	2	Monitor completion of Requery on ADP …
	3	Using recordsets with subforms to per…
	4	This Recordset is not updatable
	5	URGENT: can't update simple form p…
	6	VBA Code to copy to new record from…