Update and Append

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have created an update query which will update the table with the contents
of the fields entered into a form as follows;

UPDATE TabTapes SET TabTapes.ContractNumber =
[Forms]![TapeDispatch]![TXTContractNo], TabTapes.Customer =
[Forms]![TapeDispatch]![cmbCustomer], TabTapes.TapeID =
[Forms]![TapeDispatch]![tapeid1], TabTapes.IDate =
[Forms]![TapeDispatch]![datToday], TabTapes.RDate =
[Forms]![TapeDispatch]![datReturn], TabTapes.Returned = 'FALSE',
TabTapes.Stamped = 'FALSE'
WHERE (((TabTapes.ContractNumber)=[Forms]![TapeDispatch]![TXTContractNo])
AND ((TabTapes.TapeID)=[Forms]![TapeDispatch]![tapeid1]));

This works fine, but I also need to add new records to the table should a
user enter a 'tapeid' which does not already exist. Could anyone tell me how
I would go about doing this? I'm totally lost!


Thanks

Ann
 
AMHodgetts said:
Hi,

I have created an update query which will update the table with the contents
of the fields entered into a form as follows;

UPDATE TabTapes SET TabTapes.ContractNumber =
[Forms]![TapeDispatch]![TXTContractNo], TabTapes.Customer =
[Forms]![TapeDispatch]![cmbCustomer], TabTapes.TapeID =
[Forms]![TapeDispatch]![tapeid1], TabTapes.IDate =
[Forms]![TapeDispatch]![datToday], TabTapes.RDate =
[Forms]![TapeDispatch]![datReturn], TabTapes.Returned = 'FALSE',
TabTapes.Stamped = 'FALSE'
WHERE (((TabTapes.ContractNumber)=[Forms]![TapeDispatch]![TXTContractNo])
AND ((TabTapes.TapeID)=[Forms]![TapeDispatch]![tapeid1]));

This works fine, but I also need to add new records to the table should a
user enter a 'tapeid' which does not already exist. Could anyone tell me how
I would go about doing this? I'm totally lost!
Click to expand...

Try adding an OR to your where clause of "TabTapes.TapeID Is Null".

Normally you use an Append query for adding rows and Update queries for existing
rows, but you can get an Update query to add new rows if you structure it right.
 
Thanks for that.

The problem is that I really don't understand SQL at all. I am a total
novice. Where would I put the OR WHERE and what would I put after it. I
realise that this is probably a really stupid question but i'm totally stuck
--
Ann Hodgetts


Rick Brandt said:
AMHodgetts said:
Hi,

I have created an update query which will update the table with the contents
of the fields entered into a form as follows;

UPDATE TabTapes SET TabTapes.ContractNumber =
[Forms]![TapeDispatch]![TXTContractNo], TabTapes.Customer =
[Forms]![TapeDispatch]![cmbCustomer], TabTapes.TapeID =
[Forms]![TapeDispatch]![tapeid1], TabTapes.IDate =
[Forms]![TapeDispatch]![datToday], TabTapes.RDate =
[Forms]![TapeDispatch]![datReturn], TabTapes.Returned = 'FALSE',
TabTapes.Stamped = 'FALSE'
WHERE (((TabTapes.ContractNumber)=[Forms]![TapeDispatch]![TXTContractNo])
AND ((TabTapes.TapeID)=[Forms]![TapeDispatch]![tapeid1]));

This works fine, but I also need to add new records to the table should a
user enter a 'tapeid' which does not already exist. Could anyone tell me how
I would go about doing this? I'm totally lost!
Click to expand...

Try adding an OR to your where clause of "TabTapes.TapeID Is Null".

Normally you use an Append query for adding rows and Update queries for existing
rows, but you can get an Update query to add new rows if you structure it right.
Click to expand...
 
AMHodgetts said:
Thanks for that.

The problem is that I really don't understand SQL at all. I am a total
novice. Where would I put the OR WHERE and what would I put after it. I
realise that this is probably a really stupid question but i'm totally stuck
Click to expand...

Here in the newsgroups we use SQL for queries because we cannot post the query
design grid. You would add the "Or" clause into your query just as you would
any other criteria. For an "Or" you just make sure the new criteria in on its
own row in the design grid.
 
Hi,

I think I'm being really stupid now..

I have amended the query to be as follows;

UPDATE TabTapes SET TabTapes.ContractNumber =
[Forms]![TapeDispatch]![TXTContractNo], TabTapes.Customer =
[Forms]![TapeDispatch]![cmbCustomer], TabTapes.TapeID =
[Forms]![TapeDispatch]![tapeid1], TabTapes.IDate =
[Forms]![TapeDispatch]![datToday], TabTapes.RDate =
[Forms]![TapeDispatch]![datReturn], TabTapes.Returned = 'FALSE',
TabTapes.Stamped = 'FALSE'
WHERE (((TabTapes.ContractNumber)=[Forms]![TapeDispatch]![TXTContractNo])
AND ((TabTapes.TapeID)=[Forms]![TapeDispatch]![tapeid1])) OR "TabTapes.TapeID
Is Null";

but whenever the query runs it is now telling me that I am about to update
430 rows (IE all of the records in the database)


Hellllpppppp!
 
I'm surprised it's running: I would have expected an error from
"TabTapes.TapeID Is Null", plus the fact that you have no values after the
equal signs.

Get rid of the quotes around that. and put 0, "" or Null (as appropriate)
after the equal signs.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



AMHodgetts said:
Hi,

I think I'm being really stupid now..

I have amended the query to be as follows;

UPDATE TabTapes SET TabTapes.ContractNumber =
[Forms]![TapeDispatch]![TXTContractNo], TabTapes.Customer =
[Forms]![TapeDispatch]![cmbCustomer], TabTapes.TapeID =
[Forms]![TapeDispatch]![tapeid1], TabTapes.IDate =
[Forms]![TapeDispatch]![datToday], TabTapes.RDate =
[Forms]![TapeDispatch]![datReturn], TabTapes.Returned = 'FALSE',
TabTapes.Stamped = 'FALSE'
WHERE (((TabTapes.ContractNumber)=[Forms]![TapeDispatch]![TXTContractNo])
AND ((TabTapes.TapeID)=[Forms]![TapeDispatch]![tapeid1])) OR
"TabTapes.TapeID
Is Null";

but whenever the query runs it is now telling me that I am about to update
430 rows (IE all of the records in the database)


Hellllpppppp!
--
Ann Hodgetts


Rick Brandt said:
Here in the newsgroups we use SQL for queries because we cannot post the
query
design grid. You would add the "Or" clause into your query just as you
would
any other criteria. For an "Or" you just make sure the new criteria in
on its
own row in the design grid.
Click to expand...
Click to expand...
 
Back
Top