the . means that whatever is after the . needs to already exist, otherwise you will get an error at compile time. It also has the benefit of providing a dropdown so you can select the required value. The ! means that whatever is after yhe ! does not have to exist at compile time, but must exist at runtime otherwise you will get a runtime error.
Consider this code
Code:
dim rs as dao.recordset
set rs=currentdb.openrecordset("select * from sometable")
while not rs.EOF
rs!fieldname1=something
rs.movenext
wend
The recordset exists as an object (you have declared it with the dim statement) at compile time so you can use . - recordsets have a number of predefined properties such as EOF and movenext
But the fields do not - that won't occur until runtime when the rs object gets populated - so you have to use ! when referring to the fields
At the top of every module you should have Option Explicit and when you make changes, compile the code before running it. This will trap all compile errors such as not declaring an object, duplicate names, typo, etc. You can have this set automatically for new modules by going into the tools>options in the vba window and ticking 'require variable declaration'
If you don't compile then all compile errors will occur at runtime - Access will compile then run the code.
So use the . not the !. If you typed rs.fieldname1=something then this will fail at compile time.
With regards your main question, really need to see the full query, but from the little provided I would guess that you have blank fields. Also trying to rationalise yo saying '
unless the name of the color in the first table is Blue.' But looks like Brown has the same issue
again -would need to see your query and also perhaps your table. Best guess for why is you (or someone) had removed Blue from your table