Results 1 to 9 of 9
  1. #1
    ramkitty is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8

    split form select records based on a criterea

    Hi all, first post..
    I had been using excel to track some basic data on some assets but it has grown to big and I require better query abilities. anyhow I am using access 2007 and my database is like this
    table: assets; serial number, model, purpose, etc


    some of those such as model and purpose are related to other tables that list models and purposes
    I have a form where I have me and a few other people make changes. It basically just limits access to stuff like serial number but...

    It is a split form so it shows all records at a time but I want to be able to filter the shown records based on the purpose and/or model I just need help how to get it to show the list of records that meet the critera.

    thanks all
    Last edited by ramkitty; 03-12-2010 at 05:33 PM.

  2. #2
    ramkitty is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    I have got it mostly solved. I can filter on one critera using a combo box to look in the model which on change triggers vba code
    Form_Assets.Form.Filter = "Purpose=" & Me.Combo143 & ""
    Form_Assets.Form.FilterOn = True

  3. #3
    ramkitty is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    Got it working using the following code

    ' on button click filter for both model and purpose
    Private Sub Command149_Click()
    'if nothing is filled
    If IsNull(Me.combo_modelFilter) And IsNull(Me.combo_PurposeFilter) Then
    Me.FilterOn = False
    Exit Sub
    ElseIf Me.combo_modelFilter <> 0 And IsNull(Me.combo_PurposeFilter) Then
    'if only model is filled
    Me.Filter = "Model=" & Me.combo_modelFilter
    ElseIf IsNull(Me.combo_modelFilter) And Me.combo_PurposeFilter <> 0 Then
    'if only purpose is filled
    Me.Filter = "Purpose=" & Me.combo_PurposeFilter
    ElseIf Me.combo_modelFilter <> 0 And Me.combo_PurposeFilter <> 0 Then
    'if both model and purpose are filled
    Me.Filter = "Model=" & Me.combo_modelFilter & " AND Purpose=" & Me.combo_PurposeFilter
    Else: Exit Sub
    End If

    Me.FilterOn = True
    End Sub

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Thanks for posting your solution, and welcome to the site. Normally somebody would have replied to your first post sooner. I guess we were slacking off today!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ramkitty is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    Quote Originally Posted by pbaldy View Post
    Thanks for posting your solution, and welcome to the site. Normally somebody would have replied to your first post sooner. I guess we were slacking off today!
    thanks... I did look for a solution first too but I didnt understand the explanation and it had child windows.

    For my first ever access database I think I have done ok. I am sure there is lots of stuff that could be improved on but I am happy for now. Now to find out the best way to get all the excel data over

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post again if you get stuck with that. Typically I would either link to the Excel file or import it, then use a query or queries to move the data into the Access tables. Obviously that would depend on the structure of both.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ramkitty is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    hmm, I didnt know I could do that
    What I did for the first set of data was restucture spreadsheet to replicate part of the table then imported it directly into the table

    The spreadsheet structure is simple
    I had a sheet for each year and all the equipment was recorded under asset nuber and serial number

    the database has a table of asset number serial number and checkboxes for years that it was seen.

    The spreadsheet obviously has duplicate data from each year as we do maintenance checks on the stuff anually but there are some gear that does not get returned

    how would you suggest i address this?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, first let's look at the table. Generally speaking, relational database design is very different than spreadsheet design. In a spreadsheet it would be very common to have a row for a piece of equipment, and columns for each year. In a database, you generally would not do that. Not sure exactly what you're dealing with, but you'd have a table for the equipment, with fields for the attributes of that piece of equipment. Then you'd have a separate table for transactions involving that equipment. Fields might include an equipment ID field that would relate to that equipment table, date, what was done, etc. More info about what your database is doing might help clarify that. A commonly used expression is that spreadsheets are "short and wide", but databases are "long and narrow". In other words, what you might do with columns in a spreadsheet you do with records (rows) in a database.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    ramkitty is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    the database is to track when we see radios in this case. we get them back every year after forest fire season and we check them out and send them back to a warehouse. they seem unable to track losses so I took it up. I do not want to track trouble reports and stuff as I want it to be very slim since we already have a database to do detailed tracking, its just to big and cumbersome to do large scale quick stuff.

    as it stands we have about 1200 radios that will be in this database.

    I dont know how to show you my database here but I created

    a table for purpose that lists well purposes fields: (id, purpose)
    a table for models fields (id,model)
    a table for conditions (id,condition)
    a table for boxes (physical hard plastic cases which store) fields: (id,box)
    and a table for assets (id, assetnum, serial (not unique), lookup fields for box, purpose, condition and model as well as yes/no boxes for years.

    I originally had a table for years but I couldnt figure out how to get the 2 to jive... and i need to get this done asap, atleast for this comming year.

    i built a split form that some of the other techs here can go in and click the checkbox for seen in 2010 for the model as well as edit the condition and box. as well as set a filter

    the spreadsheet was literally as i said. It started in 07? when I had to go over all our old records after other people who were suppsoed to track this lost the info. it consisted of serial number and asset number. in 08 we made a new sheet and did the same thing, i made a report based on records from 07 that were not seen in 08.. this continued until this year when it is getting too big and tedious for me to make the reports.

    edit: Oh and I also have 2 fields for timestamp and computer username when a record is updated... It would be nice to have the changes recorded for a history but I can add that later.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Split Form Sync up
    By jonsuns7 in forum Forms
    Replies: 1
    Last Post: 11-10-2009, 02:56 PM
  2. select records in recordset
    By sksriharsha in forum Programming
    Replies: 3
    Last Post: 09-05-2009, 11:40 AM
  3. Replies: 0
    Last Post: 06-23-2009, 03:01 PM
  4. Select a certain field of a record based on ID
    By cesarone82 in forum Access
    Replies: 3
    Last Post: 06-06-2009, 01:16 PM
  5. Replies: 1
    Last Post: 03-01-2009, 09:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums