sql "Were" driving me nuts (1 Viewer)

murray83 · Sep 25, 2017

Hi All

I have the attached database which functions for the most part fine

However I’m having trouble when it comes to the "Where" part of the update in SQL

In my previous usage of this function wasn’t in a sub form so was much easier to get a unique thing to select and tell it to just update that record

The one thing which will be unique in the instance is the TAG ID, but I’m at a loss of how to do this in a sub form

Some guidance would be very helpful

Cheers

sorry forgot to say that the where function would be used on the paste records form for BWS and GM

bob fitz · Sep 25, 2017

I think you will need to give us more info on what you are trying to do.

murray83 · Sep 25, 2017

bob fitz said:
I think you will need to give us more info on what you are trying to do.

true sorry

OK when you have inputted data into the GM or BWS paste records sub form and press add, the date is inputted automatically but at the moment i don't have the "where" in my SQL query so it updates the whole dam table

I just need guidance on how I would use it in a sub-form and only update the records i'm adding on not those already present in the main table

moke123 · Sep 25, 2017

its not very clear what your trying to do. it is also very difficult to try and troubleshoot when you have ribbons hidden.

from what i can tell your subform is bound to the same table as what your trying to update. why is the update necessary?

murray83 · Sep 25, 2017

OK have commented the code for hiding ribbon

the subform is like it is so people can paste updates all in one go

and when people press "Add Records" once they have pasted into the "Paste Records Table" it should add the date for just those where at the moment due to the lack of ( and not sure how to work it in ) where clause in teh SQL it updates the whole table

moke123 · Sep 25, 2017

and when people press "Add Records" once they have pasted into the "Paste Records Table"

There is no "Paste Records Table"

I dont see any primary key in the dataset for the form so its hard to see what the relationship is.

normally your sql would look something like...
"Update BWS set [Date] = #" & Now() & "# where YourPrimaryKey = " & Your formsPrimaryKey"

Also note that "Date" is a reserved word and should not be used as a field name.

murray83 · Sep 26, 2017

ok its not called paste records sorry its called "BWS Upload" or "GM Upload"

moke123 · Sep 26, 2017

what exactly is the form supposed to do? I think i'm confused by your use of the term "Paste". Is this a data entry form to add new records or is it used to update existing records? Once the form is dirtied and closed the record is saved so your cancel button is useless once the form is dirty. Why not include the date in the form with a default value of now?

with your code below:

change the name of your date field. Date is a reserved word and should not be used as a field name.

also, when you turn warnings off you MUST turn them back on when your procedure has run

you cant update a record that has not been created yet so a where clause wont do any good.

Code:

Private Sub cmd_AddGM_Click()

'removes the msg box asking are you sure you want to add the record
DoCmd.SetWarnings False

'updates the record in main table with current date
DoCmd.RunSQL ("Update GM set [Date] = Now()")

'adds records to table for GM
Me.Query1_subform.Requery
Me.Visible = False
DoCmd.OpenForm "GMMain"
Forms!GMMain.Form.Requery


End Sub

murray83 · Sep 27, 2017

moke123 said:
Why not include the date in the form with a default value of now?[/CODE]

i did and it works

why was i trying to make it more complicated using SQL is a mystery :banghead:

cheers for help

sql "Were" driving me nuts (1 Viewer)

murray83

Games Collector

Attachments

bob fitz

AWF VIP

murray83

Games Collector

moke123

AWF VIP

murray83

Games Collector

Attachments

moke123

AWF VIP

murray83

Games Collector

moke123

AWF VIP

murray83

Games Collector

Similar threads

Users who are viewing this thread