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

Topic

New Reply

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?

Reply | Quote

Replies

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 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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

… WHERE Keywords = “BLUE, GREEN, INDIGO” …

Reply | Quote

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;

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

I haven’t seen the new thread yet…

Reply | Quote

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

Thanks again

Reply | Quote