Adding records to table from a form, with pre-populated values

[nickb]

I'm trying to construct a form that will let me translate pre-defined "base"
sentences to several languages. I have the following tables:

BaseTexts: ID, Text
Languages: Language
Translations: BaseTextID, Language, Translation

but the Translations table only has records if a translation actually
exists. So I have a query that gets me all (BaseTextID, Text, Language)
triplets for which a translation is needed (Text <-> BaseTextID is 1-1
mapping, but users need to see the text itself in order to translate), and I
want to present a multiple-records form with 4 columns:
BaseTextID, Text, Language, Translation. The first three fields should be
pre-populated with the result of my queries - so that if I have 200 texts
missing translations I will have 200 rows, then someone will fill the 200
missing translations and the Translations table will be updated with new
records.

How do I do that?

 

[Marshall Barton]

I'm trying to construct a form that will let me translate pre-defined "base"
sentences to several languages. I have the following tables:

BaseTexts: ID, Text
Languages: Language
Translations: BaseTextID, Language, Translation

but the Translations table only has records if a translation actually
exists. So I have a query that gets me all (BaseTextID, Text, Language)
triplets for which a translation is needed (Text <-> BaseTextID is 1-1
mapping, but users need to see the text itself in order to translate), and I
want to present a multiple-records form with 4 columns:
BaseTextID, Text, Language, Translation. The first three fields should be
pre-populated with the result of my queries - so that if I have 200 texts
missing translations I will have 200 rows, then someone will fill the 200
missing translations and the Translations table will be updated with new
records.

How do I do that?

I think something like this will display the information:

SELECT BT.ID, BT.Text, BT.Language
FROM (SELECT X.ID, X.Text, Language
FROM [BaseTexts] As X, Languages) As BT
LEFT JOIN Translations
ON BT.ID = Translations.BaseTextID
WHERE Translations.BaseTextID Is Null

Then you can use a subform bound directly to the
Translations table. The subform control's LinkMasterFields
property would be set to
ID,Language
and the LinkChildFields property:
BaseTextID,Language

The subform can have the Visible property of text boxes
bound to the BaseTextID and Language field set to No and
only display the Translation text box.

 

[Daryl S]

Nickb -

If you want records that don't exist in the Translations table, try this:

SELECT BaseText.ID, BaseText.Text, Languages.Language
From BaseText, Languages
WHERE Not Exist (Select 'X' from Translations WHERE BaseTextID = BaseText.ID
and Language = Languages.Language)

If the records exist, but have no Translation, then you want this:

SELECT * from Translations where Translation is null;

 

[nickb]

Thanks, it nearly got me there, only that now I see the subform like a
datasheet per record of the original form, whereas my original intention was
to have the "main" form displayed as a datasheet, so that whoever does the
translation sees one sheet with all sentences that need to be translated, row
per sentence. Is that possible?

I'm trying to construct a form that will let me translate pre-defined "base"
sentences to several languages. I have the following tables:

BaseTexts: ID, Text
Languages: Language
Translations: BaseTextID, Language, Translation

but the Translations table only has records if a translation actually
exists. So I have a query that gets me all (BaseTextID, Text, Language)
triplets for which a translation is needed (Text <-> BaseTextID is 1-1
mapping, but users need to see the text itself in order to translate), and I
want to present a multiple-records form with 4 columns:
BaseTextID, Text, Language, Translation. The first three fields should be
pre-populated with the result of my queries - so that if I have 200 texts
missing translations I will have 200 rows, then someone will fill the 200
missing translations and the Translations table will be updated with new
records.

How do I do that?

I think something like this will display the information:

SELECT BT.ID, BT.Text, BT.Language
FROM (SELECT X.ID, X.Text, Language
FROM [BaseTexts] As X, Languages) As BT
LEFT JOIN Translations
ON BT.ID = Translations.BaseTextID
WHERE Translations.BaseTextID Is Null

Then you can use a subform bound directly to the
Translations table. The subform control's LinkMasterFields
property would be set to
ID,Language
and the LinkChildFields property:
BaseTextID,Language

The subform can have the Visible property of text boxes
bound to the BaseTextID and Language field set to No and
only display the Translation text box.

 

[Marshall Barton]

If the LinkMaster/Child properties are set correctly, the
subform should only display a new record row, not a row for
each missing translation for all main form records. But if
you want the main form to be in datasheet or continuous
view, it's a moot point because datasheet and continuous
forms can not have subforms.

Your goal seems to be very difficult to achieve because you
want to link to records that do not currently exist so there
is nothing to bind to.

I have never used them, but the only (remote?) possibility
that comes to mind would be to setup a subform as a
subdatasheet.

I feel like I have a blind spot here so I hope anyone else
with a good idea jumps in here.
--
Marsh
MVP [MS Access]

Thanks, it nearly got me there, only that now I see the subform like a
datasheet per record of the original form, whereas my original intention was
to have the "main" form displayed as a datasheet, so that whoever does the
translation sees one sheet with all sentences that need to be translated, row
per sentence. Is that possible?

I'm trying to construct a form that will let me translate pre-defined "base"
sentences to several languages. I have the following tables:

BaseTexts: ID, Text
Languages: Language
Translations: BaseTextID, Language, Translation

but the Translations table only has records if a translation actually
exists. So I have a query that gets me all (BaseTextID, Text, Language)
triplets for which a translation is needed (Text <-> BaseTextID is 1-1
mapping, but users need to see the text itself in order to translate), and I
want to present a multiple-records form with 4 columns:
BaseTextID, Text, Language, Translation. The first three fields should be
pre-populated with the result of my queries - so that if I have 200 texts
missing translations I will have 200 rows, then someone will fill the 200
missing translations and the Translations table will be updated with new
records.

I think something like this will display the information:

SELECT BT.ID, BT.Text, BT.Language
FROM (SELECT X.ID, X.Text, Language
FROM [BaseTexts] As X, Languages) As BT
LEFT JOIN Translations
ON BT.ID = Translations.BaseTextID
WHERE Translations.BaseTextID Is Null

Then you can use a subform bound directly to the
Translations table. The subform control's LinkMasterFields
property would be set to
ID,Language
and the LinkChildFields property:
BaseTextID,Language

The subform can have the Visible property of text boxes
bound to the BaseTextID and Language field set to No and
only display the Translation text box.

 

[MrsUgh]

Nick-
I'm no expert, but this is how I did it...maybe someone can refine it.
Note: I added a LangID to the Languages table.
1. Created a form with a combo box, row source is the fields from the
languages table. Called the form frmSelectLanguage.
2. Create a query called qryTranslations that filters on the language
selected on the frmSelectLanguage: SELECT tblTranslations.*,
tblLanguages.*
FROM tblLanguages LEFT JOIN tblTranslations ON tblLanguages.LangID =
tblTranslations.Language
WHERE (((tblLanguages.LangID)=[forms]![frmSelectLanguage]![Combo0]));
3. Create another query using the "find unmatched" wizard, then add
the other fields needed. Ended up like this: SELECT
tblBaseText.BaseTextID, tblBaseText.BaseText,
qryTranslations.Translation, qryTranslations.tblTranslations.Language,
qryTranslations.BaseText
FROM tblBaseText LEFT JOIN qryTranslations ON tblBaseText.BaseTextID =
qryTranslations.BaseText
WHERE (((qryTranslations.BaseText) Is Null));
4. Used form wizard to create a continuous form based on the query
made in #3. Call it frmAddTranslations. Change the form properties to
not allow additions.
5. In the "After Update" property of the Translation field, enter the
following code: Private Sub Translation_AfterUpdate()
Me![Language] = Forms![frmSelectLanguage]![Combo0] ' the Language
field is the FK from the Languages table in the Translations table
Me![qryTranslations.BaseText] = Me![BaseTextID] 'set the BaseText
FK to the corresponding Base Text PK
End Sub
6. In the frmSelectLanguage, I set the Combo Box After Update property
to open the form frmAddTranslations.

I hope this helps.
Jill