Posts by W_Scott_Taylor

Re: Enable fields in form

Does it do this only if you tab out of the control versus clicking?

Re: Using checkboxes to generate a list

Assuming you are using a table(subform) in your form that contains the checkbox and label data since we are talking about 100 records the UDF would look something like the code below where field zero is the checkbox and field one is the value you are wishing to concatenate from a table called Table1.

Function CheckTheBoxes()
    Dim db As Database
    Dim rs As Recordset
    Dim strRcrd As String
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT * FROM [Table1]")
        
    rs.MoveFirst
    
    Do Until rs.EOF
        If rs.Fields(0) Then
            strRcrd = strRcrd & "," & rs.Fields(1)
        End If
        rs.MoveNext
    Loop


    strRcrd = Mid(strRcrd, 2, Len(strRcrd))
    rs.Close
    db.Close


    CheckTheBoxes = strRcrd
    Call MsgBox(strRcrd) 'Comment this out after testing...
End Function

If you have individual checkbox controls on your form then you would need to spin through the checkbox controls and concatenate them in a similar fashion.

Re: Using checkboxes to generate a list

Is the form in Access (bound to a table or controls on the form?), VBA, or an Excel worksheet with form or activeX controls?

Re: Average The Times Properly

The output was not text in the format the forumla expected. It is easier just to do it this way then. :smile:

Re: Average The Times Properly

I would convert everything to hours. The result you described 02:03:17 could be converted by a formula like =(LEFT(A1,2)*24)+MID(A1,4,2)+(RIGHT(A1,2)/60) where cell A1 contains the text 02:03:17. Then average all 4,000 formula results.

Re: Getting an Excel Output in Access

It is hard to comprehend what you are describing. It sounds like you may want to link into Access the raw data you used for the VLookups in creating the "form" and write a query to return the data you are interested in (the form data). You could then use a 'Make Table' query to produce a version of the records that are editable.

Re: How to paste text box value onto table

If I understand what you are asking correctly then you will have to use and update query to update your table with the text box value since it is not bound.

Re: VBA for automatically grouping rows based on text

It sounds like what you are wanting to do could be accomplished by inserting a row in your sheet where you copy over the vendor names and define the MISC vendors you describe and then use a pivot table to summarize the spend.

Re: Opening Excel Files from Access

How about this...

Private Sub Command1_Click()


 Dim xlApp As Excel.Application


'Using Excel
    Set xlApp = New Excel.Application
    xlApp.Workbooks.Open FileName:="C:\MyPath\MyFile.xlsx"
    xlApp.Visible = True
    
    DoEvents
    
'Done with Excel
    Set xlApp = Nothing


End Sub

Re: Query by form

Yes it is possible, but I don't know without play around with it how to structure the filter string. Maybe someone else can help on this one. I think I would start experimenting with containing the first fields criteria in parenthesis and using an AND and then the second fields criteria within parenthesis. That might work. :confused:

Re: Create Form/subform combining list of items that matches one record

What about something like this... (See Attached Sample)

I used a lookup instead of your option boxes and by clicking into the '[AUDIT RESULTS]' field on the form you get a list of 'Correct', 'Incorrect', and 'N/A' to select. I also created a small table for '[Case Manager]' names so that they can be assigned quickly. You can use the combo box beside of the policy number to type in or look up your policy number and jump to that record on the form. I also created a couple of queries that will add the 9 default checklist detail records to a new policy header record. I don't usually go this far in helping folks, I usually just try to point them in the right direction to help them learn. Hope this helps...

Re: Form/Subform: 1 item; multiple subitems

I think a lot of the functionality you are looking for will be inherent to the form/subform objects. You may have to create a procedure for clearing results or special criteria based operations. Check out the form example I inserted into our sample and see how far that will get you.

Re: Form/Subform: 1 item; multiple subitems

It sounds like you need to work on how your tables are dimensioned. It is difficult to understand the relationship from your short description. Is it something like this sample?

Re: Lookup function in Access

Dlookup() is the function. Use the expression builder and/or look at the Help file and you will find everything you need.

Re: Query by form

Okay, now I understand what you were trying to do. I had to tweak the code a little to get it to work in my example. Check out the attached sample.

Function Filter_List0() As Boolean


'Some tweaks to your code...
Dim VarItm As Variant, strFilter As String, dblA As Double


For dblA = 1 To List0.ListCount 'Name of listbox is List0
    If List0.Selected(dblA - 1) Then
        strFilter = strFilter & "City ='" & List0.ItemData(dblA - 1) & "' OR "
    End If
Next dblA
 
'This will build a string like "YourCriteria=2 OR YourCriteria=7 OR "_
    '- so remove the last 4 characters
If Len(strFilter) > 0 Then 'strFilter is NOT an empty string
    strFilter = Left(strFilter, Len(strFilter) - 4)
    End If
     
'Name of subform to filter is Table1 subform
Me.Table1_subform.Form.Filter = strFilter
Me.Table1_subform.Form.FilterOn = True
Me.Table1_subform.Requery
Me.Refresh
Me.Repaint


Filter_List0 = True


End Function


Private Sub Command16_Click()
   'Button to Apply listbox selection filters
    Call Filter_List0
End Sub


Private Sub Command17_Click()
    'Button to Remove filters from subform
    Me.Table1_subform.Form.FilterOn = False
    Me.Table1_subform.Requery
    Me.Refresh
    Me.Repaint
End Sub

Re: Query by form

If you have the combo/list box on the form, and that piece sounds like it is working to me, you can add more combo/list boxes for the different fields you need to query. You should be able to drag a subform object from your toolbar onto the form and follow the wizard to establish the parent/child relationships. The subform will filter based on selections made in the parent form automatically if you set it up correctly. Is this what you are asking?

Re: Filling Checkboxes on Access table using VBA

Boolean.

Check box values are true/false...

Re: Modifiy Define name range formula

Quote from Domenic

Can you attach a sample?

That's okay I thought you might know off the top of your head so to speak. I can just code adding a series for each row it's not too bad.

Thanks,

Re: Modifiy Define name range formula

Domenic,

Not to intentionally hijack this thread, but, can one use a technique like that with a bubble chart quadrant analysis graph? I have to automate a template where the user can paste data into a sheet and then I will give them the option of picking different columns to switch between X, Y and Bubble size depending on the number of columns they paste in. If I can use an offset technique I will not have to code adding each series for the rows of data pasted in. I can then move on to the rest of the functionality.

Thanks,

Re: Modifiy Define name range formula

I have never seen it used the way you are using it that's why I asked, I was thinking

=OFFSET(Table!$E$1,19,0,COUNT(Table!$E:$E)-1,2)

=OFFSET(ReferenceCell (Where From),Go Down this many,Go over this many, Return this many rows, Return this many columns)

Also your counta functions are returning numbers larger that one would expect when visually examining the worksheet. There is garbage in some of the 'apparently blank' cells the functions are counting.