Form Field Not Appending

[TKnight]

HI, I have a form that triggers an append query to a table. The query works fine but along with the data I want a field in the table to be populated by a date on the form. For some reason the date doesn't pull through when I reference it using
[Forms]![formName]!FieldName]

I tried it with a text field and that worked. I then tried it with a numeric field and that failed. Does this only work with text fields, and if so how can I get a specified date against the data that i'm appending, thanks, Tom.

 

[Mile-O]

Make a new field in your query like an expression, and set the field's value to your control:


i.e

NewField: [Forms]![frmYourForm]![ctlYourControl]

 

[TKnight]

yeah that's what i've done but it only works with a text field not a date or numeric field, thanks, Tom.

 

[Mile-O]

I had no problems

 

[TKnight]

Thanks Mile-O-Phile, I had exactly the same in my db as yours but for some reason mine spat the dummy out... In the end I copied the field containing text (that worked) changed the format to date and it worked even though if I selected a new text box from the toolbox it wouldn't work. Strange things are afoot!!
I really appreciate the help anyway, thanks... Tom.

 

[snicker]

On this subject...

Im trying to append data to a table. When I run the query it wants to append the same amount of records as I have in the table. Exmaple if I have 36 records in the Appointments table, the query will try to append 36 records with the new information. at the end of it all, Ill have 72 records in total. All I want to do is append 1 record to the appointments table with the new data.
Here is the sql I'm using.

INSERT INTO Apointments ( ApDate, SpClClient, ApCust, ApService )
SELECT Date() AS NewApDate, Forms!PaySheet!spclname AS newSpClClient, Forms!PaySheet!name AS newApCust, Forms!PaySheet!List42 AS NewApService
FROM Apointments;

Could this have to do with the fact that my table (the one being appended to) has 7 fields, but the Append query only has 4.
Any Ideas?

 

[Jon K]

There are two versions of the Insert Into statement.

To add a new record, you can use a "Values" list instead of "Select ... From a table".

INSERT INTO Apointments ( ApDate, SpClClient, ApCust, ApService )
VALUES (Date(), [Forms]![PaySheet]!spclname, [Forms]![PaySheet]!name, [Forms]![PaySheet]!List42);