Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy value to control (specific record) on subform (2000/XP)

    I think I've dug a big hole for myself here.

    I have 3 tables, tblFeatures, tblFiles, tblKeywords

    I have a form 'frmFeatures' with a continuous subform 'fsubDocuments' for data from tblFiles.

    After populating/editing the keywords (as field values in rows of tblKeywords) for the current file via a popup form I want to write the values to a field in tblFiles as a comma separated list.

    I have the list but am unsure how to write it back to the appropriate control on the form (on another subform 'fsubDocuments_Keywords').

    From the popup I think I need to return focus to the main form, focus specifically on a row in files subform then copy to the other subform which is synchronised.

    The root of this mess is I don't want to allow the users freeform entry for the keywords. We need to control them so they are consistent and build up a lookup list as new keywords are added.

    Any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    I wouldn't store a list of keywords in tblFiles. You can retrieve this list using a query. This has the following advantages:
    - The list will always be up-to-date.
    - Since the list is a calculated field, the user cannot edit it (which would cause it to become inconsistent with the records in tblKeywords).

    The attachment to <post#=301,393>post 301,393</post#> contains the code for a function named Concat that you can use to concatenate values into one long string. Copy the function into a standard module. You can then use it in expressions in queries and in the control source of text boxes on forms/reports.

    Feel free to ask for assistance with using the function.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    Cheers,

    I seem to have got that working fine, just a small hitch on accessing the data in another query.

    I have query comprising a list of documents with a keywords field (populated by the concat function).

    I'm trying to constuct some sql to form the control source for a list box (listing documents), essentially I want to add a where clause on the keywords. The where clause is constructed by looping through items in another listbox and populating the bit between the brackets of an 'IN' statement e.g. ("BLUE","GREEN","INDIGO")

    This worked where the keywords were typed in a memo field but not now where they are calculated field.

    This is the SQL for the documents list box

    strSQL = "SELECT FeatureGroup, Report, DocumentType, Feature, Filename FROM qryDocumentsWithKeywords WHERE " _
    & vbCrLf & "Keywords IN (" & strIn & ")"

    Do you know if this (using 'IN') just won't work here or if I've got something wrong?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    If you mean that you're trying to use the result of the Concat function, that won't work "as is" - it would result in an SQL string resembling

    ... WHERE Keywords IN (BLUE, GREEN, INDIGO)

    As you see, the quotes are missing from around the text strings. It would probably be possible to loop through the records in the subform to fill strIn. Or you could use something like this:

    strSQL = "SELECT FeatureGroup, Report, DocumentType, Feature, Filename FROM qryDocumentsWithKeywords WHERE " _
    & vbCrLf & "Keywords IN (" & Chr(34) & Replace(strIn, ", ", Chr(34) & ", " & Chr(34)) & Chr(34) & ")"

    You may have to tweak it to get it right - I obviously don't know what exactly strIn looks like.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    Thanks Hans, that's not quiet what I'm doing.

    I have used the Concat function to populated a calculated field in a query 'qryDocumentsWithKeywords'. I'm attempting to use this query, filtering the records returned by added a where clause using 'IN', to populate a list box.

    Using

    SELECT FeatureGroup, Report, DocumentType, Feature, Filename FROM qryDocumentsWithKeywords WHERE
    Keywords IN ("BLUE","GREEN","INDIGO") ORDER BY FeatureGroup, Report, DocumentType, Feature, Filename

    returns no records

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    Oh, OK. I suspect that you need something like this:

    ... WHERE Keywords = "BLUE, GREEN, INDIGO" ...

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    Not sure, I've got horribly confused.

    I only want the filter to return Documents containing any of the keywords picked, not all of them so its either lots of 'ORs' or an 'IN'.

    As I said before the SQL statement worked where the 'Keywords' field was an actual field (in the Documents table) not a calculated one in a query (using the concat function on tblKeywords)

    (the documents and keywords tables have a one to many relationship)

    Edit:

    I guess I should be using a totals query instead (i.e. using the raw keyword data, the the concatenated output), something like

    SELECT tblFiles.FileName
    FROM tblFiles INNER JOIN tblFileKeywords ON tblFiles.FileID = tblFileKeywords.FileID
    WHERE (((tblFileKeywords.Keyword) In ("BLUE","ORANGE","INDIGO","RED")))
    GROUP BY tblFiles.FileName;

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    Yes, it would be better to use the keywords table.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    Thanks again.

    I'm most of the way there but I think I might need to allow to the possibility of single quotes in the words (keywords) but I've started another thread for that one! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    A Totals query on the Keywords table seems to be working ok.

    Next problem is allowing for multiple single quotes in the keyword 'words' - that for another thread though <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Thanks again

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy value to control (specific record) on subform (2000/XP)

    I haven't seen the new thread yet... <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

Posting Permissions

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