Making a Text prompt from query bigger 1

[Cpreston]

Hi

I have created a query that asks for text to update a Notes field and then it asks for a WO Number (criteria).
[Enter Notes] and [Enter WO Number] which brings up 2 prompts and updates the correct fields

The query works fine however the when I run the query the prompt comes up for Enter Text but then it only gives me one single line to type in
I run the query from a form button.

I would prefer it to somehow allow it to be larger to view and perhaps allow multiple lines in some way.

I am not sure how to achieve this and I am not very good on access code, could someone point me in the right direction please. I think it may be a text box in a form but not sure how to do this so it updates in the query.

Thanks

 

[dhookom]

IMO, never use parameter prompts. Consider using controls on forms. Read faq701-6763 to see what I mean.

Duane
Hook'D on Access
MS Access MVP

 

[Cpreston]

Hi
Thanks for the reply

Being I am not up on programming and also I am trying to run an update on a Notes field based on a PO number criteria I cant see how this fits into what I am trying to achieve.

Thanks

 

[SkipVought]

My last post to you in forum68, suggested forum702

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

Create a form with a dropdown/combo box to select a work order. Add a larger text box that allows the user to enter the notes. You can add a command button using the wizard that will run a query.

Then your query might look like:

UPDATE [YourTableNameHere]
SET [YourNotesFieldNameHere] = Forms!YourFormNameHere!YourNotesTextBoxNameHere
WHERER [YourPONumberFieldNameHere] = Forms!YourFormNameHere!YourPOComboBoxNameHere;

Duane
Hook'D on Access
MS Access MVP

 

[Cpreston]

Hi

I have this working now as below

UPDATE dbo_WorksOrderHeader SET dbo_WorksOrderHeader.Notes = [Forms]![EntertextV2]![edittext]
WHERE (((dbo_WorksOrderHeader.WorksOrderNumber)=[Forms]![EntertextV2]![wonumber]));

However I now need it to append with the text, could you advise how the code should look please.
I have tried Insert Into but cant get the syntax correct.

Thanks

 

[Andrzejek]

INSERT INTO dbo_WorksOrderHeader
(Notes, WorksOrderNumber, ...)
VALUES ('ABcd', 1234, ...)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Cpreston]

Sorry not up on coding that much

I tried like this

INSERT INTO dbo_WorksOrderHeader
(Notes, dbo_WorksOrderHeader.Notes = [Forms]![EntertextV2]![edittext])
VALUES ( dbo_WorksOrderHeader.Notes = [Forms]![EntertextV2]![edittext])
WHERE (dbo_WorksOrderHeader.WorksOrderNumber)=[Forms]![EntertextV2]![wonumber]);

which did not work at all, I have tried changing the code but seem to get in deeper problems, could someone please help

Thanks

 

[Andrzejek]

The syntax should be:
[tt]
INSERT INTO TableName
(Field1, Field2, Field3, ...)
VALUES (1234, 'ABcd', ...)[/tt]

In anoter words:[tt]
INSERT INTO TableName
(List of fields in a table)
VALUES (list of corresponding values)[/tt]

List of fields in a table - all fields that you need to populate in order to insert a record, separated by comma.
list of corresponding values - values that you want to place in corresponding fields, separated by comma. Text is placed with ' around the value, Dates have # around them.

No "=" signs, no WHERE clause.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Cpreston]

OK

Thanks for the reply, I now have my query like this

INSERT INTO dbo_WorksOrderHeader ( notes )
SELECT [Forms]![EntertextV2]![edittext] AS Expr1
FROM dbo_WorksOrderHeader
WHERE (((dbo_WorksOrderHeader.WorksOrderNumber)=[Forms]![EntertextV2]![wonumber]));

I know you said NO WHERE clause but I only want it to update on a particular works order number (so they enter works order number, then the text to add into the Noted field and it should insert the text)

It now comes up with

You are about to append 1 rows, click Yes, then

WO update tool cant append all the records in the append query
It then gives more information and asks if to ignore these errors and run query anyway

Any ideas why I get this message

Thanks

 

[Andrzejek]

I assume the dbo_WorksOrderHeader is a table in your data base, and not a query, right?

And let's say you have several fields in your dbo_WorksOrderHeader table, something like:[tt]
ID
WorksOrderNumber
SomeDateField
SomeTextField
SomeNumberField
Notes[/tt]
and let's also say all fields are required, which means you must provide the values to all fields in order to insert a new record.

Your statement:
INSERT INTO dbo_WorksOrderHeader ( notes )[blue]
SELECT [Forms]![EntertextV2]![edittext] AS Expr1
FROM dbo_WorksOrderHeader
WHERE (((dbo_WorksOrderHeader.WorksOrderNumber)=[Forms]![EntertextV2]![wonumber]));
[/blue]
may work if (blue SELECT part works, and) the only required field in your table is Notes. and all other fields are NOT required and could be NULLs.

If this is NOT the case, you must provide values to all required fields in your table in order to INSERT a record.

Also, with your statement you run into another potential problem:
If you just run the BLUE portion of your statement, (SELECT part) you may discover that for some WorksOrderNumber you may get more than just one record, you may get 20 records. That all depends of how your table is set up. So you may end up Inserting 20 records into your table.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

On the second 'read':

"I only want it to update on a particular works order number (so they enter works order number, then the text to add into the Noted field and it should [red]insert[/red] the text)"

I would say: it should update/modify the text.

So you do not need INSERT statement, your Update statement should work just fine. They do not provide you with the new record, they just UPDATE the Notes field in your table.

Just display what you already have in this field, user can add to it, and you just save it (Update) what they end up with.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Cpreston]

Hi

I have an Insert form working kind of however, it oeverides what is already in the field.
Ideally I want it to update and add to any existing text or just insert in if null.

How do I get it to update existing text if there and not overide it.

I am using a form where the text field is the worksorernumber and the te3xt field is what text I want to update

UPDATE dbo_WorksOrderHeader SET dbo_WorksOrderHeader.Notes = [Forms]![EntertextV2]![edittext]
WHERE (((dbo_WorksOrderHeader.WorksOrderNumber)=[Forms]![EntertextV2]![wonumber]));

If I could get it to addd to existing text instead of over ride that would be ideal.

any ideas please

 

[dhookom]

Cpreston,
If what you have works, you could try:

UPDATE dbo_WorksOrderHeader 
SET [Notes] = [Notes] & " - " & [Forms]![EntertextV2]![edittext]
 WHERE WorksOrderNumber=[Forms]![EntertextV2]![wonumber];

Also, please use TGML (the little icons above the posting box) to format your posts for readability.

Duane
Hook'D on Access
MS Access MVP

 

[Cpreston]

Thanks that worked great. Many thanks for all the replys