Cannot get Append/Update queries to work..Please Help

[dougmeece]

Morning Everyone...

I have a table that needs to be append to and also updated. All the fields in the table are populated with data from the text boxes and combo boxes on a form. The Date Submitted defaults to 1/1/00 if the cboSubmittedBox field is "No". Otherwise it is selected from a calendar. The Date Entered field defaults to the current date. There are 3 sets of criteria that need to be checked for the update or append to take place.

Table Fields
Title, TitleID, Year Created, Submitted, Submitted To, Website, Type, Accepted, Date Submitted, Date Entered

All are based on one combo box with only two options.

1)

If  Me![cboSubmittedBox] = "No" Then
                Dim stAppend As String
                stAppend = "Created_Submitted_Work"
                DoCmd.OpenQuery stAppend, acNormal, acEdit
 

This option sets all the fields in the table to "Not Applicable" except the Title, TitleID, Year Created, Date Submitted and Date Entered fields.

2)

If  Me![cboSubmittedBox] = "Yes" Then
                HERE IS WHERE ADDITIONAL CODE NEED TO GO
                BUT I CANNOT GET IT TO WORK.  CODE WILL NOT
                RECOGNIZE THE TABLE AND FIELDS I USE AND A
                DLOOKUP ONLY GRABS ONE RECORD.  THE 
                PROBLEM IS THE SAME FOR THE NEXT EXAMPLE.
                Dim stAppend As String
                stAppend = "Created_Submitted_Work"
                DoCmd.OpenQuery stAppend, acNormal, acEdit

3)

If  Me![cboSubmittedBox] = "Yes" Then
                HERE IS WHERE ADDITIONAL CODE NEED TO GO
                BUT I CANNOT GET IT TO WORK.  CODE WILL NOT
                RECOGNIZE THE TABLE AND FIELDS I USE AND A
                DLOOKUP ONLY GRABS ONE RECORD.  THE 
                PROBLEM IS THE SAME FOR THE PREVIOUS EXAMPLE. 
                Dim stAppend As String
                stAppend = "Created_Submitted_Update_Query"
                DoCmd.OpenQuery stAppend, acNormal, acEdit

What I need to do is have examples 2 and 3 query the table to the words "Not Applicable" in the any of the fields that contain that text, for example the website field. If the website field does not contain "Not Applicable" then the append query will run. If it does contain "Not Applicable" then the update query will run.

I cannot get the VB code to recognize the table "Created_Submit ted" and the field "Website". It would be coded as [Created_Submitt ed.Website] in an if statement such as If [Created_Submitt ed.Website] = "Not Applicable" Then...

Doing that doesn't work though. It just doesn't even run the append or update queries.

I have been fighting this off and on for a couple of weeks and am at my wits end. Any help you all could give would be greatly appreciated.

[missinglinq]

Your duplicate posting of this question/problem has been deleted! Please refrain from this prohibited behavior in the future.

From FAQs

Do Not Double Post Your Questions

Double posting is where you start a thread on a topic and then for some reason start another thread on exactly the same topic in the same forum. Please do not do this because

• It makes it very hard for people to answer you especially if there are answers happening in all the threads you have started because they have to read 2 or more threads in order to see what has already been said.
• It swamps the forum with your problem resulting in less attention for the other threads.

If you feel for some reason that you post has been overlooked (if, for instance, it hasn't had any replies) please do not repost the question. Post a message to the thread you started, this will bump it back to the top of the thread list for the forum.

Thank you for your attention in this matter.

Linq ;0)>

[NeoPa]

Doug, it seems that this can be (and probably should have been) broken down into a requirement to update / append depending on the existing contents of the data.

It's all very difficult to understand as there is so much that's misleading and items in the code that are misnamed, but if I understand you correctly you have to append the special (Not Applicable) records when the cboSubmittedBox is set to "No", but when it's set to "Yes" we need to Append/Update depending on the current data.

I won't ask why the "Yes"/"No" control is a ComboBox rather than a CheckBox. I assume you have some reason that is not relevant to this question (Even if not then it's not a problem - simply more complicated than necessary). I just thought it may help to bring this to your attention.

I suppose I should next state that there is no facility in SQL to "Add or Update". A fairly frequent requirement is to do this depending on whether or not the PK of the record is matched. I can't tell what your PK is in this case so giving any definitive answer will be impossible. Unfortunately, this facility isn't available anyway, so we will need code to support the following logic :
If [cboSubmittedBox]="No" then Append anyway, with some fields set to "Not Applicable".

Otherwise:
    If a record is found that matches the [TitleID] and has "Not Applicable" in ANY of the five fields (just check one) then update this record
    Otherwise:
        Add the record as a new one.

Finally, the correct way to reference the item would be [Created_Submitt ed].[Website] rather than [Created_Submitt ed.Website] as you had it.

[dougmeece]

Sorry about the double post. In the first I had entered the word "Thanks" at the end of the subject and I thought that might lead people to think that I had my answer and was thanking people for the help.

NeoPa,

I am so far removed from VB and Access that I am basically starting over. I am sure I have many things wrong in my code and I appreciate any and all critiques.

I have tried entering the code the way you described but it didn't work either. I worked around the problem but I know it isn't very efficient. I just gave each option its own command button and coded to make it work. I know that is sloppy but it was all I could get to work.

Thanks for the help.

[NeoPa]

I hear you Doug, and I sympathise.

I would just make a comment though that will hopefully provide some guidance.
It is generally better in the long run, to organise the process (in your head or on paper) at the start. This will save a deal of grief later on. Understanding the requirement and the process clearly makes coding it so much easier and straightforward .

As for your attempts at getting the logic to work, if you have any specific questions we can help with I'd be glad to see what I can do.