Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: Keep Some Values or DB reconfig

    I just got an update for the project I am working on. I have a master table with 11 or so fields, I also have a look up table for categories. I was just told the people would put in data for each category at once. All but 5 would change. So like the the date started and completed would stay the same, but the category and details about what was found would change. Can I do this or do I need to redo the whole structure?
    Ryan
    My Blog

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sorry Ryan but could you maybe rephrase this? I'm having difficulty understanding the question.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Paul- in different terms can I create something like a session variable? Can I make a subform from a flat table? Or do I need to redo my db?

    Say I am making large used car dealership form, that tracks new shipments of cars, which the dealership has multiple locations. When the creator of the db made the db, he wasn't told each location gets 10-20 cars at a time, he thought 2-3 tops at a time. So the table is flat, other than the location table. Say when a car shipment comes in the car manufacturer is the same, is there a way to keep some of the information from the last entry, or does there need to be a subform?
    Ryan
    My Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You could open a recordset and look at previous record?
    Or I suppose you could use public variables but it certainly depends on the environment (number of concurrent users etc).

    Also, what do you mean by "the table is flat"?
    Can you describe the table structure (perhaps even give us a few rows of example data)?
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    How would I do that George? (Open a RS or public variables). The chances of more than 2 people using it at the same time at the minute is silm. Now if it gets distribute to other locations, there would be issues.
    Ryan
    My Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What about table structure?

    Public variables are declared in modules and can be accessed throughout a project. Recordsets can be used to look at the data in your tables, you can then assign values of fields in your tables to variables. Either way you can look up what the last value was and use it anywhere you want.
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Wink

    George here is a sample. The first 7 fields, and the end one stay the same, but the rest change, ie different category, finding, plan. I would like to keep the 8 the same, till maybe I close the form.

    I am working on reconfiguring it, but I don't want to reimport the records -- a nightmare
    Attached Files Attached Files
    Ryan
    My Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I knocked this up quickly - but it needs some serious work
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub btnAddFinding_Click()
    On Error GoTo Err_btnAddFinding_Click
    
    Dim rs As DAO.Recordset
    Dim dB As Database
    Dim SQL As String
    
        DoCmd.GoToRecord , , acNewRec
    
        SQL = ""
        SQL = SQL & " SELECT ReviewDate, InitReportDate"
        SQL = SQL & " FROM tblFindings"
        
        Set dB = CurrentDb()
        Set rs = dB.OpenRecordset(SQL)
    
        rs.FindLast (1 = 1)
        Me.ReviewDate.Value = rs.Fields("ReviewDate")
        Me.InitReportDate.Value = rs.Fields("InitReportDate")
    
    Exit_btnAddFinding_Click:
        rs.Close
        Set rs = Nothing
        Set dB = Nothing
        Exit Sub
    
    Err_btnAddFinding_Click:
        MsgBox Err.Description
        Resume Exit_btnAddFinding_Click
        
    End Sub
    Please DO NOT simply cut and paste this.
    Make sure you UNDERSTAND it first!
    If you have any questions about it let me know!

    EDIT: Did I mention that I left a little bug in there, just in case you did just copy paste :evil:
    Last edited by gvee; 07-20-07 at 14:41. Reason: added evilness >:
    George
    Home | Blog

  9. #9
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    How evil is evil? The only thing I see is that you said ReviewDate instead of RevDate.

    Edit Nope not that
    Last edited by rguy84; 07-20-07 at 16:40.
    Ryan
    My Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Far more evil
    What I wrote works, but it doesn't work well.

    If you explain to me what you understand about it I'll happily point you in the right direction
    George
    Home | Blog

  11. #11
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    You click the button, it adds a new rec.
    It makes a temp table, grabs the associated fields.
    Grabs the last value in the query.
    Shoves the respective values into the controls.

    ---
    I think I know what I did... I added the other controls, but forgot to update the query. The only thing that I don't know about is the 1=1 in findlast. I didn't really find any examples of that really. So that's the only thing that's making me scratch my head.
    Ryan
    My Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •