Hello,

I am receiving an invalid data type error when passing a value from a form control (text box) to a named data macro parameter in my table. The form control value is getting its value through a TempVar. I have almost this exact same setup working elsewhere in my database. The only difference is that in the working version, the form control is using a control source directly tied to a field, whereas in this version the control source is using the TempVars collection to display the temporary variable (Control Source = [TempVars]![MyVariable]). Please see attached image.

The source of the TempVar is the same data type as the table field. So why the error?

Have you run it through the debug process to ensure the varialbes are what you expect in terms of value and data type?

Yes, I have. I think the problem stems from the fact that the Control Source value in the form ([TempVars]![CurrentProjectID]) has NO data type, and therefore can't be matched by the lookup field in my table that contains the matching item. Thus, the error message "The field 'ProjectID' cannot accept the data type supplied by Setfield".

I have tried adding the original table that contains the lookup field and doing some fancy-schmancy cross-referencing, but to no avail. Because this is a New record, I can't get the matching item to even show up, let alone reference it.

How do you pass a value through a form, through a data macro and into a table when both start and end values come from the same table?

I haven't worked a ton with the macros. I generally use VBA. When doing your comparison, you should be able to change the datatype. From the Visual Basic for Applications Window hit help, then search on Type Conversion Functions to find different functions for converting data types.




Not sure if that helps or not (IMG:style_emoticons/default/smile.gif)

JT

Sorry, this is a web database, and VBA is not allowed.

Let me try this in a more visual way. I prepared a graphic to explain what it is I am trying to do. Basically, the powers that be wish to track project status so that reports can be generated to determine how long a project was in a given status. Please see the attached image.

Please feel free to tell me I am doing it all wrong. (IMG:style_emoticons/default/smile.gif)

OK, I solved my problem. I had been avoiding using the ProjectID field as a control source for my Status Change form because the form was designed to open to a new record, and that field would have shown as blank. I realized I could set a macro for the OnLoad event that used my TempVar to pull the matching ProjectID into the control. Basically I used LookupRecord, WHERE [Projects].[ProjectID] = [TempVars]![CurrentProjectID], then SetProperty Value = [TempVars]![[CurrentProjectID]. I also did not realize I could put a value other than zero or one in the value for SetProperty. You just have to prefix it with the equal sign.

Geez, this stuff is HARD.