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

[ryantam626]

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

[June7]

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

[ryantam626]

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

[June7]

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"?

[ryantam626]

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

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.

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?

[June7]

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 ..."

[ryantam626]

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

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

[June7]

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.

[ryantam626]

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!!

[June7]

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.

[ryantam626]

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.

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

[June7]

I don't see anything wrong.