Passing Form Value to Append Query

[CharlesWhiteman]

Dear All, I have a problem which I need to solve and am in need of a clue!

I have a table which contains lots of line items relating to quotes. Each quote usually contains three line items and is prefixed in all cases with a number which relates to number of users. For example

5 5 User Software Details Price Cost Note

in the table there are up to 100 users and each has line 3 items. I automate quote generation by using a form. In the form a quote reference is generated and a text box for the number of users. I have a command button which then, based on the value of users text box, selects from the line items table and sends the records to my TblQuotes.

My Tbl quotes is exactly the same structure as my line items table except it also has a field for the quote reference which is in the form.

Where I am stuck is I cant seem to get Access to send the quote reference into the TblQuotes as part of the append query.

Please help!

 

[pbaldy]

What have you tried? You should be able to refer to the form control in the SELECT or VALUES clause.

 

[CharlesWhiteman]

answer

Hi Paul, I'm using Access QBE window and am not doing it in Vb. I've tried adding it and referring to it in QBE but with no success. Here's a view of QBE window.

 

[pbaldy]

What exactly does that say? My old eyes can't make it out, and blowing up the picture makes it too blurry to read. This works in a production db of mine:

ResNum: [Forms]![frmMainMenu]![txtResNum]

 

[CharlesWhiteman]

a bigger picture..

Here it is again but a little larger.

 

[pbaldy]

You have it reversed there. To put a value in the table from the form, you'd want the form reference in the "Field", and the target Field in the "Append To".

 

[CharlesWhiteman]

Thanks for your last reply which I implemented and came up with the same error on execution. A type conversion failure.

I tested further by creating a new query but leaving out the ID field and left out of the query the reference to the number of users and it worked. As soon as I made a criteria entry to select only the number of users as denoted by the number entered in the form I got the same error which results in the correct line items being appended but the quote reference not being appended.

 

[Rabbie]

Thanks for your last reply which I implemented and came up with the same error on execution. A type conversion failure.

Check that you have the same data time in the value you are wanting to append as the field you are inserting it into.

 

[pbaldy]

As Rabbie suggested, the type conversion error implies that the data coming from the form is incompatible with the data type of the field in the table. What is the data type of the field in the table, and what is contained in the form field (the bound column if it's a combo)?

 

[CharlesWhiteman]

Example Attached

Having looked and tried to investigate the issue I thought I would attach an example of the Db, much of it has been taken out to downsize.

Basically, when creating a new quote theuser should be able to click 'add set' enter the number of users and then add to quote and thats where the error is occuring. Any thoughts?