Advertisement

06.11.2008 at 01:51PM PDT, ID: 23477421
[x]
Attachment Details

State.Value where state is a field not a condition

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

Tags: Microsoft, Access, 2000, ADP

I have a connection to an SQL database and want to grab the value of a field called state.

The State field appears in a subform that is populated (filtered) when a combo box is changed.
The subform is filtered on the active selection of the combo box, pretty standard access function.

I want to grab the value of the state field in the subform and run a couple of checks it before it is written to the database:

one of the checks requires another lookup function

Strsql = "Select state, owneship from vOwneshipCheckByST where state = '" & State.value & "' "

the problem that I am having is that state is an intrinsic constant and subsequently calls something completely different... like me.state if me was a form would return open or closed.

The question is, how do I pull the value of State from the active record in the database?

The syntax works for all other columns, I think that the conflict is in the field name "state"

Any help is greatly appreciated
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:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Strsql, State, Message As String, vLimit, i, counter As Integer, rs As ADODB.Recordset
 
If IsNull(Owneship.Value) Or Owneship.Value > 1 Then
    MsgBox "Ownership column Can not be Blank or have a value greater than 1.0 ", vbOKOnly, "Missing Info"
    Reported.Value = 0
    Owneship.Value = Null
Exit Sub
End If
 
 
Strsql = "Select state, owneship from vOwneshipCheckByST where state = '" & State.Value & "' "
Set rs = CurrentProject.Connection.Execute(Strsql)
 
vLimit = 1
If rs("Owneship") + Owneship.Value > 1 Then
    
    If MsgBox("The Combined Ownership of all dealers in this state has exceeded 100%" & vbCrLf & _
        "Would you like to see a list of all dealers in this state and their marketshare percentages?", vbYesNo, "MarketShare Exceeded!") = vbYes Then
        
        State = State.Value
        Strsql = ""
        Set rs = Nothing
        'Strsql = "Select count (*) as count from vOwneshiopCheck where state = '" & state & "'"""
        
        Strsql = "Select * from vOwnershipCheck where state = '" & State & "'"
        Set rs = CurrentProject.Connection.Execute(Strsql)
        
        Message = ""
        While Not rs.EOF
        Message = Message + Trim(rs("ReportingName")) & " has marketshare of " & rs("Owneship") & "." & vbCrLf
            rs.MoveNext
        Wend
        
        MsgBox Message, vbInformation, "Market Share distribution for: " & State
    
    End If
Reported.Value = 0
Owneship.Value = Null
End If
 
 
End Sub
[+][-]06.11.2008 at 01:57PM PDT, ID: 21764110

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.11.2008 at 02:40PM PDT, ID: 21764353

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.11.2008 at 02:50PM PDT, ID: 21764417

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.11.2008 at 03:03PM PDT, ID: 21764496

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 Access Database, Microsoft ADP, Access Forms
Tags: Microsoft, Access, 2000, ADP
Sign Up Now!
Solution Provided By: jimhorn
Participating Experts: 2
Solution Grade: A
 
 
[+][-]06.11.2008 at 06:18PM PDT, ID: 21765453

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.12.2008 at 07:58AM PDT, ID: 21770165

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.12.2008 at 08:01AM PDT, ID: 21770208

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