Solved Form based on query + subform

[Andy74]

Hello,

I have a form based on a query that shows something like "open orders to be invoiced". I would like to add a subform to allow users to write comments, something like below. The subform is based on a table (on SQL server) and I set three fields to be linked to the top query: customer code, month, year, so that each time a comment is added a new record would be added to the table, referring to that specific customer, year and month.

Problem: when I start the main form I get message error "-3004", somethin like "reserved error".

Is there anything wrong in my approach? Any ideas how to solve this or maybe get the same result with a different approach?

Thnaks

 

[Gasman]

Always best to say the *exact* error?
I would not show username or DataCR. Just populate that when the record is saved. Just have comments for entry.
Can you enter direct to the linked table?

 

[Andy74]

Always best to say the *exact* error?
I would not show username or DataCR. Just populate that when the record is saved. Just have comments for entry.
Can you enter direct to the linked table?

hello, the error was simply "-3004 - reserved error". I don't have a screenshot, but it's in italian language.
Anyway I solved the issue by changing the linked fields from customerCode, Year, Month to customerName, Year, Month. Now it works.

 

[Pat Hartman]

Year and Month are really poor choices for column names since they are the names of functions. Always avoid using function or property names as column names. You end up with nothing but problems once you have to write code.

 

[plog]

I set three fields to be linked to the top query: customer code, month, year,

Agree with Pat, plus the above sounds like a poor relationship. Why not the primary key of the ultimate datasource the main form is based on instead of 3 fields? Or just the Order number? Is that comment really suppose to go to the customer/month/year? Or is that comment related to the order?

Plus, what happens when I fill out the form, fill out comments, then later realize the month is wrong and change it? Comments are no longer associated to it.

 

[Andy74]

Year and Month are really poor choices for column names since they are the names of functions. Always avoid using function or property names as column names. You end up with nothing but problems once you have to write code.

thanks, I didn't think about it, I will change the names

 

[Andy74]

Agree with Pat, plus the above sounds like a poor relationship. Why not the primary key of the ultimate datasource the main form is based on instead of 3 fields? Or just the Order number? Is that comment really suppose to go to the customer/month/year? Or is that comment related to the order?

Plus, what happens when I fill out the form, fill out comments, then later realize the month is wrong and change it? Comments are no longer associated to it.

thanks but the main form is based on a aggregated query, so there is no primary key on it. It looks like below, so the only choice I had is to use the 3 fields I mentioned.