SQL statement UPDATE table issue

[gori1084]

Hi everyone,



I've set up a form to input information in a table. The code is still kinda new so there are a few issues that I'm aware of. The thing I'm primarily interested is an SQL query which keeps on failing no matter how I arrangeit. Here is the code I'm using:

Private Sub cmdEntryData_Click()

Dim db As DAO.Database
Dim fd As DAO.Recordset
Dim SQLNAV As String

Set db = CurrentDb
Set fd = db.OpenRecordset("FundsData")
      For i = 1 To 12
        If Me.CboSelectData = "NAV" Then
            fd.AddNew
            fd!SponsorID = TxtHiddenSponsorID
            fd!MM = MonthName(i, True)
            fd!YY = TxtYYDataEntry
            fd.Update
        End If
    
    Next
    
    For i = 1 To 12
    If Not IsNull(Controls("Mon" & i)) Then
    SQLNAV = "UPDATE FundsData SET NAV = " & Controls("Mon" & i) & " WHERE SponsorID=TxtHiddenSponsorID and YY = TxtYYDataEntry and MM = MonthName(i, True)"
    DoCmd.RunSQL SQLNAV
    End If
    
    Next
    fd.Close
    
End Sub

This is the part that gives the most problems:

SQLNAV = "UPDATE FundsData SET NAV = " & Controls("Mon" & i) & " WHERE SponsorID=TxtHiddenSponsorID and YY = TxtYYDataEntry and MM = MonthName(i, True)"
DoCmd.RunSQL SQLNAV

I think the issue specifically is with this statement: MM = MonthName(i, True) since when I click to enter data, a dialog window appears asking me to enter a parameter value for i. Now I used already two for statements so I don't understand what the issue is, especially because the previous i [Controls("Mon" & i)] appears to be working fine. I'm pretty sure it's a syntax thing but I'm learning as I go and this leaves me puzzled.

Thanks!!

[pbaldy]

i is unknown outside VBA, so you have to concatenate the value into the string, as you did previously. When the SQL gets passed to the database engine to be processed, it barfs on the unknown, hence the parameter prompt.

[gori1084]

Thank you but to be honest with you, I don't quite understand. Would you be able to correct the code based on your observation? I have no backgriound whatsoever in SQL so I have a hard time making technical observations resonate. Thanks!

[pbaldy]

I could, but I like to teach how to fish. You need to do the same type of thing you did here:

...SET NAV = " & Controls("Mon" & i) & " WHERE...

here:

MonthName(i, True)

[gori1084]

Dude I appreciate it but for someone that doesn't know how to code, like I said, i simply don't know what to do. If you prefer, I'll write here all the questions I have on the syntax. For instance, what are the '" all over the place? Why there are a million ampersands?
People like me struggle a lotto put together something that even remotely works so unless you provide some kind of background on how to do it, you can't assume I know what you are talking about because I don't and I spent already three hours trying to learn how to fish....

[gori1084]

It's a totally different syntax, there there is monthname and I donno how to deal with that...

[rpeare]

What are you attempting to do with this code?
why are you cycling through months twice?

do you have controls on your form that are named MON1 through MON12?

[pbaldy]

Didn't even want to try?

...MonthName(" & i & ", True)...

[gori1084]

Sure, I have the whole day trying to do something i tried already for three hours. If you do not know how to do one thing, you just don't. The syntax you proposed is probably great, I just could have never gotten there before another few hours of frustration. Thank you though, now if this works I may know something for next time.

@rpeare: you've been really helpful to me the last few days. As you know, I don't really understand what I'm doing in detail since I never coded anything before so I welcome any suggestions. To be honest with you, I try to fix an issue at the time and learn by doing. But if it doesn't make any sense, please, by all means, I welcome any suggestion that helps me get a better grip on coding.

thanks!

[rpeare]

That doesn't really answer any of my questions.

Step back from the problem and describe what you want to do with this section of code.

[gori1084]

Ok rpeare. Here it is. I'm also attaching the DB for ease of reference.


UserName: 556494
PW: cesar

So here is what I'm trying to do. In the form FundsData I pick a counterparty from the list box and then create entry in a table called funds data, which is the third in the hierarchy so to speak: Sponsors->Counterparties->FundsData

The form FundsData serves as data input for the table FormsData. I set up a combobox (CboSelectData) that lists the fields I want to populate through the 12 months textboxes below. Becasue YY and MM (year and Month) are new fields (I can't import them in the table from anywhere) I created the code to populate all the MM field with the Month associated with each of the 12 textboxes and the year as indicate in the textbox called TxtYYDataEntry.

Now, with the code provide by pbaldy, the values in the NAV field are populated correctly. Now I need to:

- Create something like a recordcheck (i suppose) so that a new series of month (the 12 textboxes) is only included if the year is not already there
- I would like the 12 textboxes to populate with any existing values in the FundsTable so that the user does not enter a new value for a month/year that already has one simply because he doesn't see it
- Extend the same process to Performance (the other item in the drop down combox)
- Finally, I would need to address the login box issue (in a separate form). Once I log in the field UserRegion is populated in the form SponsorsNew (you created it ), if I open another form or anything, the field goes blank and I have to log in again, which defies the purpose of the loginbox because this is supposed to preven users from seeing records outside their region so that field USERREGION should always remain populated, in order to preven users from accessing data they shouldn't see.
- Finally, i would need to extend the same logic currently in the form sponsorsnew to all other forms, so that the user can consistently see only data associated with his/her region.

This is quite a mouthful but since you're so competent, I figured I'd throw in all that's leaving me scratching my head.

Thank you so much!!!

[rpeare]

can't read 2013 files from my current computer will have to wait until monday.

[gori1084]

No worries, thanks for being so kind and supportive. I really appreciate it. There's not much i can do to show it other than adding you reputation though!

Here's the most recent version of the DB.

EDIT: i deleted the file I upoloaded in this post as you already had the file as of last week. Sorry about this.

[rpeare]

There's no fundsdata form in this version of the database
There is a fundsdata table howefver but your question implies a form

Are you talking about the form 'fromdataupdate' if so how do I navigate to that form using the buttons/controls on your main form?

If so there are a number of problems with the ON CLICK event of the UPDATE RECORDS button

1. You are adding 12 months every time you click it regardless of circumstance
2. You are cycling through items twice and you are not providing the proper context to update your data correctly
3. You SQL statement to perform the update is putting text markers (') around number fields, you YY field and your SponsorID field specifically

The way you are going with this relies on there to be only ONE possible combination of SponsorID/CounterpartyID

If that's the case what I would recommend is that when you access your data you limit the information to the Sponsor/Counterparty you're interested in using a Generated SQL statement like:

sSQL = "SELECT * FROM FundsData WHERE [SponsorID] = " & TxtHiddenSponsorID & " AND [YY] = " & TxtYYDataEntry & " AND [CounterpartyID] = " & CPList & " ORDER BY [MM]"

In other words you are only interested in looking at the data related to a specific sponsor, counterparty and year.

You can then count the records in this (you really only care if there is actually any data or not) using

if fd.recordcount = 0 then
    'this is where you would add the 'basic' records if you are determined to do it the way you are currently
else
    'this is where you would perform your updates of existing records, again using the combination of sponsor, counterparty, year and month
endif

finally I would switch your AFTER UPDATE event of your list box to the ON CLICK event, and instead of just populating the hiddensponsorID text box you also look up the values and populate the correct year/month fields on your form.

[gori1084]

Thank you Rpeare. So I think you are overrating my mastery of VBA/SQL but i did figure out a few things. First your questions:

Are you talking about the form 'fromdataupdate' if so how do I navigate to that form using the buttons/controls on your main form?

Yes, sorry

You are adding 12 months every time you click it regardless of circumstance

I think i fixed it now, I make a check if Mon & i is empty. If empty, no update.

You are cycling through items twice and you are not providing the proper context to update your data correctly

I eliminated the second For and it works just fine. My problem is that I screw up the syntax in my end if statements so.... i added a second "For" to get around the issue

You SQL statement to perform the update is putting text markers (') around number fields, you YY field and your SponsorID field specifically

I did not correct this but does not seem to affect. Can you perhaps clarify the difference between enclosing a word in "Word" vs 'Word'?

The way you are going with this relies on there to be only ONE possible combination of SponsorID/CounterpartyID

Correct

sSQL = "SELECT * FROM FundsData WHERE [SponsorID] = " & TxtHiddenSponsorID & " AND [YY] = " & TxtYYDataEntry & " AND [CounterpartyID] = " & CPList & " ORDER BY [MM]"

What does this SQL do? i get the function but where are the results displayed? I'm not sure we are on the same page here. I would use it to populate those cells that have data entered for a given month already. Thing is, I don't know how to tell Access to search if in the fundsdata table there is already a value for any given ID/MM/YY. Your syntax suggests something different...

You can then count the records in this (you really only care if there is actually any data or not) using:

if fd.recordcount = 0 then
    'this is where you would add the 'basic' records if you are determined to do it the way you are currently
else
    'this is where you would perform your updates of existing records, again using the combination of sponsor, counterparty, year and month
endif

Can you please explain how this would be useful?

Here below I post the latest DB.

Alternatives DB - V1.1.accdb.zip
thanks a million!!!