[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Runtime Error 3188

I have a form based on a query which displays data relating to a work log, one of the fields is a comments memo field.
The user can add comments using another unbound form that updates the comments field via an SQL statement.
Every time I run the SQL I get the runtime error 3188. Any Ideas ?

I have just noticed that if my form is not filtered in any way the sql statement works fine, problem only happens when a value is entered (log no) in a filter field on the form, the after update event requery's the query to display only the records that satify the log number.

0
Duggie
Asked:
Duggie
  • 3
  • 2
1 Solution
 
snavebelacCommented:
Could you post the code used to generate your sql stetament or the sql statement itself.

C
0
 
DuggieAuthor Commented:
sql = "UPDATE ticket_tbl SET ticket_tbl.comments = """ & comments & """ WHERE ticket_tbl.ticketid = Forms![comments_frm]![text4]"
DoCmd.RunSQL sql
0
 
snavebelacCommented:
Try this . ..

DoCmd.RunSQL "UPDATE ticket_tbl SET ticket_tbl.comments = '" & comments & "' WHERE ticket_tbl.ticketid = " & Forms![comments_frm]![text4]

C
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DuggieAuthor Commented:
Generates a syntax error 3075, the comments variable can be a very long string that is generated from a txt file using
Set objstream = objFSO.opentextfile("c:\temp\comments.txt")
comments = objstream.Readall
thats the reason I used the """ quotes to force it to read as a string.
I know the sql statement works I just can't suss out why it thinks the table is locked.
The query the form is based on looks at 4 tables request_tbl, ticket_tbl, stage_tbl & priority_tbl resulting in a query called veiwreq_qry.
The code updates 3 different fields in 2 different tables.
sql = "UPDATE request_tbl SET request_tbl.amended=#" & Now() & "# where request_tbl.ticket_no = Forms![comments_frm]![text4]"
DoCmd.RunSQL sql
sql = "UPDATE ticket_tbl SET ticket_tbl.comments = """ & comments & """ WHERE ticket_tbl.ticketid = Forms![comments_frm]![text4]"
DoCmd.RunSQL sql
sql = "UPDATE ticket_tbl SET ticket_tbl.datemodified=#" & Now() & "# where ticket_tbl.ticketid = Forms![comments_frm]![text4]"
DoCmd.RunSQL sql
But only breaks down on the 2nd SQL query, ie the update to the request_tbl.amended field works fine.
relationship between request_tbl and ticket_tbl is one to many.

0
 
DuggieAuthor Commented:
Think I have solved it code now reads as follows

Forms![viewreq_frm].RecordSource = ""
sql = "UPDATE ticket_tbl SET ticket_tbl.comments = """ & comments & """ WHERE ticket_tbl.ticketid = Forms![comments_frm]![text4]"
DoCmd.RunSQL sql
sql = "UPDATE ticket_tbl SET ticket_tbl.datemodified=#" & Now() & "# where ticket_tbl.ticketid = Forms![comments_frm]![text4]"
DoCmd.RunSQL sql
Forms![viewreq_frm].RecordSource = "request_qry"

Removed the recordsource from the form, updated the ticket_tbl.comments box and than replaced the recordsource on the table, sure there will be a better way but this seems to work.

But thanks for your efforts C

Regards
Duggie

0
 
ee_ai_constructCommented:
Question answered by asker or dialog valuable.
Closed, 125 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now