regarding manipulation of data in combobox

[ritimajain]

hi i have form which has a 2 cascading combo box one having CELL selection and other picking values from field SCTYPE having values UL or OL and some Values in table are blank means SCTYPE IS NULL from where combo box is picking values related to above cell selection in combo box. I want if values are null to above cell selection UL automatically comes in combo box so that user don't get confused seeing blank value in combobox . how can i do that ?

[Dal Jeanis]

Okay, let me see if I have been able to decode the quesiton -

The first combo box is selecting a field called CELL, based on values available in a table. When a value is selected for CELL, it must be in the table, and then the second combo box will be populated.

The second combo box reads against the same table for records with the value in CELL, and returns a list of possible SCTYPEs for records with that CELL.

Possibility A) There can only be one record, and it has either Null, blank, UL or OL.

RULE A1) If the record is null or blank, update the record to UL and display UL. Otherwise, display whatever is there.

RULE A2) If the record is null or blank, display UL but do not update the record. Otherwise, display whatever is there.

Possibility B) There can be multiple possible records, including records with Null, records with blank, records with UL, and records with OL. The combo boxes are used to select records for a subform.

If multiple records are available, and one of them is null or blank, you want the default to be UL, without yet limiting the display of the subselect.

Are either of those possibilities close to your question? If not, then please give us a slow, clear description of what the function of the combo boxes should be.

[ritimajain]

for second combobox it can be null or blank, or ul or ol
i want if there is null or blank SCTYPE in table corrosponding to 1st combobox which is CELL, UL comes automatically so that user dont get confused seeing blank selection in SECOND combobox and result which already present in table related to NULL SCTYPE display In textbox . I m able in displayon result in textbox realted to blank selection of SCTYPE

[Dal Jeanis]

Your second description sounded exactly like your first one. No new information.

The pictures do help me get more specific with my questions.

1) Are the records with Nulls wrong? Do you want to fix them to be UL? That can be done with an update query like this:

UPDATE POWERCONTROL1
SET SCTYPE = "UL"
WHERE SCTYPE Is Null OR SCTYPE = "";

(As always, test the code in a test version of your database before doing to the real database.)


2. Is the second combo box being used to ADD records if they aren't there? If so, then it shouldn't be bound to anything at all, and shouldn't depend for tis load values on what is in the table. Load it with the three values, and set the default to UL.

(You could also test for the existence of a UL record, if present default to UL, if not present, test for existence of OL, if present, default to OL, else default to UL.)

[ritimajain]

second combobox is realted wth other fields of same or different table ,no matter they are null or not, sctype field is common to many tables and many tables are linked through SCTYPE field thats why i can't update it .. give me way out to do so in code of form

[Dal Jeanis]

There are a lot of different ways to do anything in Access. Being helpful consists of understanding a person's current setup and methods, and giving them pointers that will solve their problem, while being consistent with their current methods and with the needs of their business.

Usually, I can figure out what someone is trying to do, and answer their questions based on the intrinsic characteristics of the business they're trying to support.
Here's what is confusing me:

1) You show a screen that has three dropdown boxes, one for BSC, one for CELL site, and one for SCTYPE.

2) The area below that is set up to display a single record, not more, no less.

3) You are telling me that Null or Blank in SCTYPE can be a valid record.

4) You are telling me that Null or Blank in SCTYPE confuses your users.

Given the above, I have no idea what you are trying to achieve.

You want to show a blank/Null record but you want to fool the user into thinking it's a UL record? But it's not really a UL record?

Perhaps you could load the dropdown with an unchanging list of four unique values "UL", "OL", "UL(blank)", "UL(Null)". Make it an unbound control, and in the AfterUpdate of the control, set the value of an invisible textbox control to the value that has been selected, and requery to load the record that was desired.

I could give better advice if I understood the way the form was going to be used for workflow.

Off hand, I'd probably do something like, in the afterupdate of the CELL, use Dcount to determine if any Ul existed, in which case set the combo box to select UL, set the textbox to UL and load the UL record into the date field.

If UL doesn't exist, do that for OL. If OL doesn't exist, do that for blank. If blank doesn't exist, do it for null.

That, of course, is assuming that this isn't going to be used for initially entering the data.

Sorry, it takes way too much time to guess what you're up to, so if you can't explain it, slowly and clearly, then we can't tell you what to do.

[ritimajain]

ok it seems i am unable to explain my problem.ok i will try it once more
see my form has three comboboxes. User first select bsc, on the basis of its selection second combobox has related cell , then user will select second combobox , on the basis of second selection , user have sctype selection which can be UL or OL but in tables from where i m picking records from sctype field some are null and some have ul or ol and i want to tell you one more thing if it is null it means they come under ul type , i only want if it is null , combobox display UL so that user dont get confused by seeing blank selection in combobox , by third selection of combobox , various text boxes are filled which comes from various tables and this SCTYPE field, cell and bsc field are used to linked various table
is this helpful for you?

[ritimajain]

I am uploading my db may be it will help you alot in understanding my problem

[Dal Jeanis]

Then this statement applies -

Perhaps you could load the dropdown with an unchanging list of four unique values "UL", "OL", "UL(blank)", "UL(Null)". Make it an unbound control, and in the AfterUpdate of the control, set the value of an invisible textbox control to the value that has been selected, and requery to load the record that was desired.

In other words, your dropdown box gets loaded with a display value that is not what is in the record. Therefore your dropdown box cannot be bound to the field.

[ritimajain]

sorry i am new to access can you tell me how can i do this, in my db , i have uploaded my db in my last post

[Dal Jeanis]

1) Change Combo4 Properties to the following

Row Source         "UL";"OL";""
Row Source Type    Value List
Bound Column       1
Limit to List      Yes

2) Replace the entire VBA code module of the form with the following:

Option Compare Database
Option Explicit

Private Sub Combo0_AfterUpdate()
   Combo2.Value = ""
   Combo2.RowSource = _
      "Select DISTINCT POWERCONTROL1.CELL " & _
      "FROM POWERCONTROL1 " & _
      "WHERE POWERCONTROL1.EXCHID = '" & Combo0.Value & "' "
End Sub


Private Sub Combo2_AfterUpdate()
   Dim ULCount As Integer
   Dim OLCount As Integer
   Dim strULCount As String
   Dim strOLCount As String
   ' Set up query conditions
   strOLCount = "(   ([EXCHID] = '" & Combo0.Value & "') " & _
                 "AND ([CELL] = '" & Combo2.Value & "') " & _
                 "AND ([SCTYPE] = 'OL') " & _
                 ")"
   
   ' uncomment this line to display SQL
   'MsgBox "OLCount SQL is" & vbCrLf & strOLCOunt
   strULCount = "(   ([EXCHID] = '" & Combo0.Value & "') " & _
                 "AND ([CELL] = '" & Combo2.Value & "') " & _
                 "AND (([SCTYPE] = 'UL') OR (NZ([SCTYPE],'') = ''))" & _
                 ")"
    
   ' uncomment this line to display SQL
   'MsgBox "ULCount SQL is" & vbCrLf & strULCOunt
   ' verify that ULs or OLs exist
   ULCount = DCount("[CELL]", "[POWERCONTROL1]", strULCount)
   OLCount = DCount("[CELL]", "[POWERCONTROL1]", strOLCount)
   
   ' uncomment these lines to display counts
   'MsgBox "OLCount is" & OLCount & " and  ULCount is " & ULCount
   If ULCount > 0 Then
      Combo4.Value = "UL"
      Call LoadValues(strULCount)
   
   Else
      If OLCount > 0 Then
         Combo4.Value = "OL"
         Call LoadValues(strOLCount)
      Else
         Combo4.Value = ""
         Call ClearValues
      End If
   End If
   
   
End Sub


Private Sub Combo4_AfterUpdate()
   Dim ULCount As Integer
   Dim OLCount As Integer
   Dim strULCount As String
   Dim strOLCount As String
   ' Set up query conditions
   strOLCount = "(   ([EXCHID] = '" & Combo0.Value & "') " & _
                 "AND ([CELL] = '" & Combo2.Value & "') " & _
                 "AND ([SCTYPE] = 'OL') " & _
                 ")"
   
   ' uncomment this line to display SQL
   'MsgBox "OLCount SQL is" & vbCrLf & strOLCOunt

   strULCount = "(   ([EXCHID] = '" & Combo0.Value & "') " & _
                 "AND ([CELL] = '" & Combo2.Value & "') " & _
                 "AND (([SCTYPE] = 'UL') OR (NZ([SCTYPE],'') = ''))" & _
                 ")"
   
   ' uncomment this line to display SQL
   'MsgBox "ULCount SQL is" & vbCrLf & strULCOunt
 
   ' verify that ULs or OLs exist
   ULCount = DCount("[CELL]", "[POWERCONTROL1]", strULCount)
   OLCount = DCount("[CELL]", "[POWERCONTROL1]", strOLCount)
   
   ' uncomment this line to display counts
   'MsgBox "OLCount is" & OLCount & " and  ULCount is " & ULCount
   
   
   ' If the selected SCType exists, load it,
   ' otherwise pop message and clear
   Select Case Combo4.Value
      Case "UL"
         If ULCount > 0 Then
            Call LoadValues(strULCount)
         Else
            MsgBox "No UL records are present"
            Call ClearValues
         End If
   
      Case "OL"
         If OLCount > 0 Then
            Call LoadValues(strOLCount)
         Else
            MsgBox "No OL records are present"
            Call ClearValues
         End If
   
      Case Else
         Call ClearValues
      
   End Select
   
  
End Sub


Private Sub ClearValues()
   Text6 = ""
   Text8 = ""
   Text10 = ""
   Text12 = ""
   Text14 = ""
   Text16 = ""
   Text18 = ""
   Text20 = ""
   Text22 = ""
   Text24 = ""
            
End Sub


Private Sub LoadValues(strOLUL As String)
   Dim strBasic As String
  
   strBasic = "(([EXCHID] = '" & Combo0.Value & "') " & _
              "AND ([CELL] = '" & Combo2.Value & "'))"
   Text6 = DLookup("[SSDESUL]", "[POWERCONTROL1]", strOLUL)
   Text8 = DLookup("[LCOMPUL]", "[POWERCONTROL1]", strOLUL)
   Text10 = DLookup("[QDESULAFR]", "[POWERCONTROL2]", strBasic)
   Text12 = DLookup("[MSTXPWR]", "[POWER]", strOLUL)
   Text14 = DLookup("[CLSRAMP]", "[CLS]", strBasic)
   Text16 = DLookup("[SCLD]", "[SUBCELL]", strBasic)
   Text18 = DLookup("[DTXD]", "[SYSINFO]", strBasic)
   Text20 = DLookup("[FBOFFSP]", "[LOCATING1]", strOLUL)
   Text22 = DLookup("[PSSTA]", "[LOCATING2]", strBasic)
   Text24 = DLookup("[IHO]", "[IHO]", strOLUL)
End Sub

I believe you will find the new behavior acceptable.

Lessons to learn from this code -

1) When possible, build your SQL in a string variable in such a way that it can be reused in different places.

2) Table name is not strictly necessary in the conditions parameter of a Dlookup... which means that you only have two distinct conditions to test for, the ones WITH SCTYPE (passed in variable strOLUL), and the ones without SCTYPE (built in strBasic).

3) This code tests for UL, Null, and empty string:

(([SCTYPE] = 'UL') OR (NZ([SCTYPE],'') = ''))

4) This whole issue can be corrected (and should be corrected, and would have avoided most of this code) if you were to run updates on all the tables to change blanks/nulls to UL, since you have specified that blanks/nulls are equivalent to UL.

5) When you're doing the same thing in multiple places, you can build a called routine so that the code isn't duplicated unnecessarily.

[ritimajain]

thank you so much .. u solved my problem ..!