Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2009
    Posts
    37

    Unanswered: add field to a table based on other field

    Hello, I'm kind of stuck with this.
    I want to add fields to a table where the fieldname is based on another table.

    Let me explain.
    I have three tables:
    - Dog (where DogID is the primary key)
    - Sightings (where SightingID is the primary key)
    and
    - Dogs at Sighting (with ID as primary key, and the other fields are SightingID and a field for each Dog)

    For every Dog there should be a field in the 'Dogs at Sighting' table.
    So, every time a new dog is added to the 'Dog' table a new field in the 'Dogs at Sighting' should be added with the DogID as fieldname.

    However...It doesn't seem to work with the ALTER TABLE statement because you have to manually type the fieldname. In this case that's NOT desirable.

    At the mo I'm a bit lost how to solve this, hope you guys can help me out!

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    I'm not certain I completely understand your situation but I suspect you should alter your table design so that you are adding records to the [Dogs at Sighting] table and not new fields.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Feb 2009
    Posts
    37
    That could be a solution.
    But then I have to solve this:
    The DogID are listed as numbers or text (in the old database they're text and meaningful...but I'd like to get red of meaningful ID's).
    But when I enter data of a sighting I'd like to select the DogID's by tickboxes so I don't have to enter all the ID's every time but just tick them of.

    Could this be possible?

  4. #4
    Join Date
    Mar 2007
    Posts
    277
    You could simply select them from a MiltiSelect ListBox.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree multiselect list or combo box is probably the way to go....

    ...or depending on the number of dogs you could create a form filled with checkboxes created on the fly based on the dogs table. not something for the feint hearted, but it would work. would require a fair bit VBA behind the scenes.


    You may want to revisit your original desin to say sub classify dogs by say breed, so you are not having to wade through every dog to find the right one. bear in mind its a trade off. you need to make it as easy as possible for your users to find the data they want in the dogs table. if you have say 20..50 dogs a simple table works, if you have many dogs it may make sense to filter out all except say Manchester Terriors, or Alsations to find the right dog.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2009
    Posts
    37
    Well, there're about 200 Dogs now in the table, but more will be added throughout the year.
    Every Dog belongs to a pack (I'm talking about African Wild Dogs here) so I'll look into filtering in combination with a multiselect list first.
    I think that could work, I'll give that a try first.
    If not I might have to look into creating a form filled with checkboxes created on the fly, but 'would require a fair bit VBA behind the scenes' does frighten me a bit (hence my nickname).

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If not I might have to look into creating a form filled with checkboxes created on the fly
    Don't go down that path. To the dark side it leads.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Feb 2009
    Posts
    37
    Well I'm trying to do it with a mulitlist box.
    Creating the listbox based on pack-name (so it only gives the dogid's in that particular pack) is no problem.

    Saving the selected DogID's to the table 'Dogs at Sighting' in the field DogID is...

    With some google-ing I came to this code:
    Code:
    Function AppendListboxSelectionsToTable()
    'loop through Listbox
    Dim lstCount As Long
    Dim strWhere As String
    Dim lstVar As Variant
    lstCount = Me.DogsINPack.ItemsSelected.Count
    
    For Each lstVar In Me.DogsINPack.ItemsSelected
       CurrentDb.Execute "INSERT INTO [Dogs at Sighting (DogID) SELECT '" & Me.DogsINPack.ItemData(lstVar) & "' AS appendItem"
    Next lstVar
    End Function
    I've stored this as a module (VB...) and apparently I have to call this procedure on closing or after update of the form and that's where I get lost.

    I have to put in some script in the form at 'after update' or 'closing' but what code I don't know...

    Maybe I just don't see the obvious (sunday night 2 AM here, working on it now for several hours after attending the 60th wedding anniversary of my grandparents earlier this night so I'm not at my sharpest now...)

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    From your form, go to Properties and find After Update while the form is still selected. Select [Event Procedure] from there and a code window will appear.

    That is where you need to have this code.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Feb 2009
    Posts
    37
    If I put it there nothing seems to happen...No error message, but the data is not added to the table either...
    I thought I had to write some new code there to start the code from the module.
    But what this new code should look like I don't know

  11. #11
    Join Date
    Feb 2009
    Posts
    37
    I've gotten a little further in a slightly different way.

    I've created the multi select box which upon entering first asks me to enter a pack name to filter the list of DogIDs (listbox is named lstDogIDs).
    The I put a button (named cmdOK on the form.

    In this button I put the following code on the click event:
    Code:
    Option Compare Database
    Option Explicit
    Private Sub cmdOK_Click()
    On Error GoTo Err_Handler
    ' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    ' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryDogsSelected")
    ' Loop through the selected items in the list box and build a text string
    If Me!lstDogIDs.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstDogIDs.ItemsSelected
    strCriteria = strCriteria & "Dogs.DogID = " & Chr(34) _
    & Me!lstDogIDs.ItemData(varItem) & Chr(34) & "OR "
    Next varItem
    strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
    MsgBox "Must Select An Item From The List First"
    Exit Sub
    End If
    ' Build the new SQL statement incorporating the string
    strSQL = "INSERT INTO DogsatSighting ( DogID ) " & _
    "SELECT Dogs.DogID FROM Dogs " & _
    "WHERE " & strCriteria & ";"
    ' Apply the new SQL statement to the query
    qdf.SQL = strSQL
    ' Open the query
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDogsSelected"
    DoCmd.SetWarnings True
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
    
    Exit_Handler:
    Exit Sub
    
    Err_Handler:
    MsgBox Err.Description
    Resume Exit_Handler
    
    End Sub
    And this work fine. I select the the DogIDs in the multi select list box and when I press the button it saves those DogIDs to the table DogsatSighting in the DogID field.
    But...
    I also want to save the SightingID to which the DogIDs correspond (for that particular sighting).

    In the end I will have a sighting-form, on which the SightingID will be automatically generated. In this form I will select the DogIDs which then should be stored together with the corresponding SightingID in the DogsatSighting form.

    What would be the way to go?
    I've been looking into modifying the INSERT INTO statement, but I can't see a way to use this statement from different tables.
    Or should I use a subform?

  12. #12
    Join Date
    Feb 2009
    Posts
    37
    I just wrote a select query and converted it to an append query which works fine.

    The select-query, qrySelectDogsatSigthing:
    Code:
    SELECT Dogs.DogID, Sightings.SightingID
    FROM Dogs, Sightings
    WHERE (((Sightings.SightingID)=[Sighting]) AND ((Dogs.Current)=[Pack seen]));
    The append query, qryAppendSelectDogsatSighting:
    Code:
    INSERT INTO DogsatSighting ( DogID, SightingID )
    SELECT Dogs.DogID, Sightings.SightingID
    FROM Dogs, Sightings
    WHERE (((Sightings.SightingID)=[Sighting]) AND ((Dogs.Current)=[Pack seen]));
    But I have to update the VB code, which is not my greatest strength.

    I've already adjusted the INSERT INTO statement in the following code (the bold part hopefully correct) but I also have to adjust some other parts of the VB code to put the selected SigthingID in the string.

    Code:
    Private Sub cmdOK_Click()
    On Error GoTo Err_Handler
    ' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    ' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryAppendSelectDogsatSighting")
    ' Loop through the selected items in the list box and build a text string
    If Me!lstDogIDs.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstDogIDs.ItemsSelected
    strCriteria = strCriteria & "Dogs.DogID = " & Chr(34) _
    & Me!lstDogIDs.ItemData(varItem) & Chr(34) & "OR "
    Next varItem
    strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
    MsgBox "Must Select An Item From The List First"
    Exit Sub
    End If
    ' Build the new SQL statement incorporating the string
    strSQL = "INSERT INTO DogsatSighting ( DogID, SightingID ) " & _
    "SELECT Dogs.DogID, Sightings.SightingID FROM Dogs, Sightings " & _
    "WHERE " & strCriteria & ";"
    ' Apply the new SQL statement to the query
    qdf.sql = strSQL
    ' Open the query
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryAppendSelectDogsatSighting"
    DoCmd.SetWarnings True
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
    
    Exit_Handler:
    Exit Sub
    
    Err_Handler:
    MsgBox Err.Description
    Resume Exit_Handler
    
    End Sub
    If this works then there is one more step to take I think.
    With this code I have to manually put in the SightingID and Pack name which ideally should be done automatically while filling in the form.

  13. #13
    Join Date
    Feb 2009
    Posts
    37
    I still haven't worked it out.
    Maybe somebody can help me out, I've uploaded the tables, forms and queries as I have them now in a .zip
    Attached Files Attached Files

Posting Permissions

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