changing value in subform from other subform (1 Viewer)

[bigmac]

hi all can you help please I have two subforms [SUB1] and [SUB2]on SUb1 I have two fields the first [make] can contain the following texts
gtaw
gtaw/smaw
smaw
gmaw/fcaw
the second [test date] contains dates

on sub2 I have a four fields [gtaw][gtaw/smaw][smaw]and [gmaw], all are date fields .

what I want to happen is after I update field [gtaw] on sub2 then all rows on sub1 that have "gtaw" in field [make] then the [test date] field in these rows will update to the same date as in sub2 [gtaw] how do I do this please

 

[theDBguy]

Hi. Is sub2 bound to the same table as sub1? If not, then I wonder why you would have the same information stored in two different tables.

 

[bigmac]

hi, they are not bound , sub2 is for holding a history by date and it is only opened by selected users , sub1 is always show to all users so I need to get sub1 to update with the dates placed In it

 

[theDBguy]

Then, you should be able to simply refer to sub1 from sub2. For example:


=Forms!MainFormName.Sub1.Form!DateFieldName

 

[bigmac]

I think I can handle the update to sub1 but how do I only update the rows that contain only "gtaw" in the [make] field?

 

[theDBguy]

Can you post a screenshot or a sample db? I am not sure I am following what you're doing. Sorry.

 

[bigmac]

have attached an image , front sub is sub2 , if you look I have put a date in the gtaw update column 08/01/2019, I have updated this field I want to update all test date fields in the sub1 that the [ make] fields contain gtaw

 

[theDBguy]

Hi. Thanks for posting the image. But hang on, you said the forms are unbound. From looking at the image, they look like bound forms to me. You can use an UPDATE query to update records in a table, but it appears your table structure may not be properly normalized from looking at your image.

 

[bigmac]

what do you mean?

 

[theDBguy]

For example, the fields gtaw, smaw, gmaw/fcaw, plus all the other fields ending with "update" appear to be a "repeating group." Repeating groups are in violation of normalization principles. In any case, have you tried using an UPDATE query to change your dates? Here's an example of an UPDATE query:


"UPDATE Sub1TableName SET TestDate=#" & Format(Me.gtawupdate, "yyyy-mm-dd") & "# WHERE make='gtaw'"

 

[bigmac]

ok I will try this and let you know thanks mate

 

[theDBguy]

Don't forget to consider normalizing your database, so you can avoid having to create workarounds like this.