Results 1 to 12 of 12

Use VBA to edit record or create new record in a query

  1. #1
    ryantam626 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    10

    Use VBA to edit record or create new record in a query

    Hi all!

    I am trying to use VBA to edit record or create new record in a query and I have no idea how to write the codes.

    So now I have a form(Form A), with the fields [Project ID], [Work Designation], [Type Of Work] on it. I also created a "template" query, where the criterion are the values currently on the form(Form A) (ie. [Forms]![Progress Report Form]![Project_ID] etc etc) so that whenever it is opened the records should be of the records with the same roject ID, Work Designation and Type Of Work on the form. Note that there is also a checkbox ([complete]) on the form.

    Now if record on query record does exist and checkbox is true. The record needs to copy data from the Form A, fields are [end increment] and [end date]. If checkbox is false but query record still exist, only copy field [start date] and [Start Increment]

    If record on query record does not exist and checkbox is true. A new record need to be created, copying data from the form, fields are [Project ID], [Project Name], [Type Of Work], [Work Designation], [Start Date], [End Date], [Start Increment], [End Increment]. If checkbox is false but query record still exist, well I think you get the idea already :|

    So far I have the code
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    DoCmd.Close acQuery, ("work designation table query template")
    DoCmd.OpenQuery ("work designation table query template")
    End Sub

    Which only close and open the query template to ensure the wanted record if exist to show. And is probably of no use

    Thanks in advance for the help

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    31,313
    Code would not edit or add record to query - it is table the query is based on that is edited. After data is saved to table, the query can be refreshed to reflect the changes.

    Maybe it's just late but your criteria don't really make sense to me. "Now if record on query record does exist and checkbox is true." does sound rational but "If record on query record does not exist and checkbox is true." escapes me. If there isn't a record then there isn't a checkbox. What is 'record on query record'?

    How can code close a query before it is opened?

    Use UPDATE and INSERT sql actions to edit tables programmatically. Review http://www.w3schools.com/sql/default.asp

    In VBA, like:
    CurrentDb.Execute "UPDATE tablename Set fieldname=" & Me.textboxname & " WHERE ID=" & Me.ID
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    ryantam626 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    10
    Quote Originally Posted by June7 View Post
    Code would not edit or add record to query - it is table the query is based on that is edited. After data is saved to table, the query can be refreshed to reflect the changes.

    Maybe it's just late but your criteria don't really make sense to me. "Now if record on query record does exist and checkbox is true." does sound rational but "If record on query record does not exist and checkbox is true." escapes me. If there isn't a record then there isn't a checkbox. What is 'record on query record'?

    How can code close a query before it is opened?

    Use UPDATE and INSERT sql actions to edit tables programmatically. Review http://www.w3schools.com/sql/default.asp

    In VBA, like:
    CurrentDb.Execute "UPDATE tablename Set fieldname=" & Me.textboxname & " WHERE ID=" & Me.ID
    Sorry, it's just my bad English :|
    Record on query record = Whatever comes up in the query using the criterion
    The checkbox is on a form which on updating the record, query will be opened and to update or create new record on another table.
    The code to close the query is simply to make sure the criterion are "refreshed". I didn't really try the refresh code, but I think the code works as far as I could tell.

    And thanks for the reply

  4. #4
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    31,313
    I don't understand the issue. What is the problem you are having? It is not necessary to open and close a SELECT query to 'refresh'. The query does that continually even if never opened. It is RecordSource of an open form that would need to be refreshed/requeried to reflect the table edits.

    What is the SQL for query "work designation table query template"?
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    ryantam626 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    10
    Quote Originally Posted by June7 View Post
    I don't understand the issue. What is the problem you are having? It is not necessary to open and close a SELECT query to 'refresh'. The query does that continually even if never opened. It is RecordSource of an open form that would need to be refreshed/requeried to reflect the table edits.

    What is the SQL for query "work designation table query template"?
    SQL
    Code:
    SELECT [Work Designation Table].[Project ID], [Work Designation Table].[Project Name], [Work Designation Table].[Work Designation], [Work Designation Table].[Type Of Work], [Work Designation Table].[Start Increment], [Work Designation Table].[End Increment], [Work Designation Table].[Start Date], [Work Designation Table].[End Date]FROM [Work Designation Table]
    WHERE ((([Work Designation Table].[Project ID])=[Forms]![Progress Report Form]![Project_ID]) AND (([Work Designation Table].[Project Name])=[Forms]![Progress Report Form]![Project_Name]) AND (([Work Designation Table].[Work Designation])=[Forms]![Progress Report Form]![Work_Designation]) AND (([Work Designation Table].[Type Of Work])=[Forms]![Progress Report Form]![Work_Type]));


    Hmmm... I have been trying to use the link you have provided to work out something. So far I am able to finish the updating record part. But when I am trying to finish the insert record part, I kept having syntax or run time error.


    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)' refresh query
    DoCmd.Close acQuery, ("WD_temp")
    DoCmd.OpenQuery ("WD_temp")
    ' counting record
    Dim RecordCount As Long
    RecordCount = Nz(DCount("*", "WD_temp"), 0)
    If RecordCount = 1 Then 'only one record exist which is the way it is supposed to be
    ' copy start increment when null
    Dim CopyStartIncrement As String
    CopyStartIncrement = "Update wd_temp  Set [start increment] = [Forms]![Progress Report Form]![start_Depth] where [start increment] is null "
    DoCmd.RunSQL CopyStartIncrement
    ' copy start date when null
    Dim CopyStartDate As String
    CopyStartDate = "Update wd_temp  Set [start date] = [Forms]![Progress Report Form]![date_of_work] where [start date] is null "
    DoCmd.RunSQL CopyStartDate
    End If
    If RecordCount = 0 Then 'if initally record is not entered just in case
    Dim NoRecordStart As String
    NoRecordStart = "Insert into wd_temp ([Project id],[project name],[work designation],[type of work],[start increment],[start date]) values('[Forms]![Progress Report Form]![Project_ID]','[Forms]![Progress Report Form]![project_name]','[Forms]![Progress Report Form]![Work_Designation]','[Forms]![Progress Report Form]![Work_Type]','[Forms]![Progress Report Form]![Start_Depth]','[Forms]![Progress Report Form]![Date_Of_Work]')"
    DoCmd.RunSQL NoRecordStart
    End If
    If Me.Completed = True Then 'checkbox on form is ticked
    ' copy end increment
    Dim CopyEndIncrement As String
    CopyEndIncrement = "Update wd_temp  Set [end increment] = [Forms]![Progress Report Form]![End_Depth]"
    DoCmd.RunSQL CopyEndIncrement
    ' copy end date
    Dim CopyEndDate As String
    CopyEndDate = "Update wd_temp  Set [end date] = [Forms]![Progress Report Form]![Date_Of_Work]"
    DoCmd.RunSQL CopyEndDate
    End If
    End Sub
    The code is very messy and I might tidy it up later. I am very new to this, thanks for bearing with me
    Btw, how will you write the code for refreshing the RecordSource?

  6. #6
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    31,313
    Refreshing the form RecordSource could simply be: Me.Requery

    Your SQL statements won't work. Have to concatenate variables so that when the sql runs the value of the variable is in the constructed statement, not the variable name. Reference to form control is a variable. Like:

    DoCmd.RunSQL "Update wd_temp Set [start increment] = " & [Forms]![Progress Report Form]![start_Depth] & " where [start increment] is null"

    DoCmd.RunSQL "Update wd_temp Set [start date] = #" & [Forms]![Progress Report Form]![date_of_work] & "# where [start date] is null"

    DoCmd.RunSQL "Insert into wd_temp ([Project id],[project name],[work designation],[type of work],[start increment],[start date]) values('" & Me.Project_ID & '",'" & Me.project_name & "','" & Me.Work_Designation & "','" & Me.Work_Type & "'," & Me!Start_Depth & ",#" & Me.Date_Of_Work & "#)"

    If Project ID field is a number datatype, remove the apostrophe delimiters and I presume [start increment] is a number datatype.

    Problem with DoCmd.RunSQL is will get warning message from Access that you will have to respond to every time. Can get around that by:
    DoCmd.SetWarnings False
    'code here
    DoCmd.SetWarnings True

    Or use:
    CurrentDb.Execute "UPDATE ..."
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    ryantam626 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    10
    Quote Originally Posted by June7 View Post
    Refreshing the form RecordSource could simply be: Me.Requery

    Your SQL statements won't work. Have to concatenate variables so that when the sql runs the value of the variable is in the constructed statement, not the variable name. Reference to form control is a variable. Like:

    DoCmd.RunSQL "Update wd_temp Set [start increment] = " & [Forms]![Progress Report Form]![start_Depth] & " where [start increment] is null"

    DoCmd.RunSQL "Update wd_temp Set [start date] = #" & [Forms]![Progress Report Form]![date_of_work] & "# where [start date] is null"

    DoCmd.RunSQL "Insert into wd_temp ([Project id],[project name],[work designation],[type of work],[start increment],[start date]) values('" & Me.Project_ID & '",'" & Me.project_name & "','" & Me.Work_Designation & "','" & Me.Work_Type & "'," & Me!Start_Depth & ",#" & Me.Date_Of_Work & "#)"

    If Project ID field is a number datatype, remove the apostrophe delimiters and I presume [start increment] is a number datatype.

    Problem with DoCmd.RunSQL is will get warning message from Access that you will have to respond to every time. Can get around that by:
    DoCmd.SetWarnings False
    'code here
    DoCmd.SetWarnings True

    Or use:
    CurrentDb.Execute "UPDATE ..."
    Sorry, I don't quite get why my SQL statement won't work. What is "concatenate variables"? What are the differences between my code and yours? And what does the # and & and " and ' mean? (I am self-learning these stuffs as I work my way through finishing the database, so sorry for these simple questions :|)

    The thing about
    Code:
    Me.Requery
    is that I need the query not the form to refresh, plus for some reason when I put it in, I will get a run time error because I am using BeforeUpdate.

    And no, Project ID won't be number datatype. Why the apostrophe though?

    Thanks again

  8. #8
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    31,313
    Text strings can have spaces and spaces are also recognized by SQL as word separators for the statement. The apostrophe is a delimiter that keeps the text value intact. The # character is delimiter for date values just to distinguish from text or number. Number type don't need delimiter.

    Concatenate means: to link together; unite in a series or chain. This is what must be done to construct an SQL statement in VBA when variables are involved.
    The & is concatenation operator. It tells VBA to link the values on each side into a continuous statement. Your SQL is completely within quote marks, including the reference to form textbox. The form textbox reference is then a literal string in the statement, instead of the date value that is in the textbox. My syntax will build SQL statement with the date value from the textbox.

    Example of SQL with literal value in the statement:
    "Update wd_temp Set [start increment] = 5 where [start increment] is null"
    "Update wd_temp Set [start date] = #1/10/2012# where [start date] is null"
    The same SQL to concatenate value from variable:
    "Update wd_temp Set [start increment] = " & [Forms]![Progress Report Form]![start_Depth] & " where [start increment] is null"
    "Update wd_temp Set [start date] = #" & [Forms]![Progress Report Form]![date_of_work] & "# where [start date] is null"
    Note the space before 'where' to prevent runon with the preceding value when the statement compiles.

    As I said, query should not need to 'refresh'. Is this a db for multiple users or just yourself? Users should not interact directly with tables and queries. Leave tables and queries closed. Forms and reports are for user interface with data.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    ryantam626 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    10
    Quote Originally Posted by June7 View Post
    Text strings can have spaces and spaces are also recognized by SQL as word separators for the statement. The apostrophe is a delimiter that keeps the text value intact. The # character is delimiter for date values just to distinguish from text or number. Number type don't need delimiter.

    Concatenate means: to link together; unite in a series or chain. This is what must be done to construct an SQL statement in VBA when variables are involved.
    The & is concatenation operator. It tells VBA to link the values on each side into a continuous statement. Your SQL is completely within quote marks, including the reference to form textbox. The form textbox reference is then a literal string in the statement, instead of the date value that is in the textbox. My syntax will build SQL statement with the date value from the textbox.

    Example of SQL with literal value in the statement:
    "Update wd_temp Set [start increment] = 5 where [start increment] is null"
    "Update wd_temp Set [start date] = #1/10/2012# where [start date] is null"
    The same SQL to concatenate value from variable:
    "Update wd_temp Set [start increment] = " & [Forms]![Progress Report Form]![start_Depth] & " where [start increment] is null"
    "Update wd_temp Set [start date] = #" & [Forms]![Progress Report Form]![date_of_work] & "# where [start date] is null"
    Note the space before 'where' to prevent runon with the preceding value when the statement compiles.

    As I said, query should not need to 'refresh'. Is this a db for multiple users or just yourself? Users should not interact directly with tables and queries. Leave tables and queries closed. Forms and reports are for user interface with data.
    Ahh, I see! Thanks for explaining so well
    Oh, the query is simply a mean to edit and create the desired record(s), I thought of using a query because I need to use filter through a lot of records to see if there is an existing similar one. I intend to insert a line to close the query after all of it, so it will look like nothing happened to the user.
    And the db is meant for multiple users, will that make a difference?

    If you have time you could read this, it's about the new requirements of the form.
    1.) When there is a half-filled record and if users checked the phase complete checkbox, then copy the end date and increment. (Producing a completely filled record)
    2.) When there is no half-filled record create a new record with the same field of project id, work designation etc etc. And depending on whether users checked the phase complete checkbox, copy corresponding fields. (Producing a half-filled or completely filled record)

    Thank you so much!!
    Attached Thumbnails Attached Thumbnails IMG.jpg  

  10. #10
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    31,313
    I ask if there are multiple users because most developers would design db so users cannot interact with tables and queries. Forms would be used to view, edit, filter, sort records. Also, a split design is recommended.

    Yes, I expect code could accomplish those actions. The code itself is simple and the real trick often is figuring out what event to put it in.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  11. #11
    ryantam626 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    10
    Quote Originally Posted by June7 View Post
    I ask if there are multiple users because most developers would design db so users cannot interact with tables and queries. Forms would be used to view, edit, filter, sort records. Also, a split design is recommended.

    Yes, I expect code could accomplish those actions. The code itself is simple and the real trick often is figuring out what event to put it in.
    Ah. I am experiencing a new problem now. I tried using the INSERT INTO code, but there is a run-time error saying too few parameters.

    Code:
    DoCmd.RunSQL "Insert into wd_temp ([Project ID]) values ('" & Me.Project_ID & "')"
    What do I have to change? I also tried a literal value, it still gave me an error.

    Thanks

  12. #12
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    31,313
    I don't see anything wrong.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 06-12-2012, 09:02 PM
  2. Replies: 4
    Last Post: 03-30-2012, 08:45 PM
  3. Find A Record on a Certain form to edit
    By donnan33 in forum Access
    Replies: 1
    Last Post: 02-29-2012, 12:08 PM
  4. Click a record to display it for edit
    By mapl in forum Access
    Replies: 0
    Last Post: 11-24-2008, 01:02 PM
  5. Can I edit a record with a subform?
    By bigmax in forum Forms
    Replies: 0
    Last Post: 08-11-2008, 06:55 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums