ive used MsgBox() to check all my values, they are all there which means the reference to the form field items are correct. i cant seem to find the error though ??
Hi. You run the risk of running into troubles when you use special characters in your field names. Try enclosing [City/District] in square brackets. Hope that helps...
ive used MsgBox() to check all my values, they are all there which means the reference to the form field items are correct. i cant seem to find the error though ??
dude, yes, didnt you see the screenshot of the table structure ?? i used msgbox to ensure my variables were not empty. any suggestions on how to solve the problem ??
Hi. You run the risk of running into troubles when you use special characters in your field names. Try enclosing [City/District] in square brackets. Hope that helps...
dude, yes, didnt you see the screenshot of the table structure ?? i used msgbox to ensure my variables were not empty. any suggestions on how to solve the problem ??
Bro .... im tryna help someone with a problem, string would have been the ideal format, i know that. the INSERT worked fine, now im simply tryna resolve this UPDATE issue, do you have any suggestions for this particular issue ???
What I did was took your first query from your first post and applied some simple rules. You had a DoCmd.RunSQL "statement" construct where the statement was fairly complex. However, doing it that way allows the Access environment one last chance to supply values for everything into the string. (This is actually a GOOD thing.) It does so because VBA syntax "hides" the fact that you DoCmd line is REALLY running something that internally looks like...
Code:
CALL DoCmd.RunSQL( SQLstatement, ...)
Since you have a complex string expression as the SQL statement, VBA is required by syntax rules to evaluate the expression and reduce it to a static string before passing it. All substitutions occur in the VBA environment because that is where that evaluation occurs. Normally, you don't think of it that way, but that is actually what is happening. VBA is just being "nice" about interpreting the statement. This implied CALL is what has to happen "behind the scenes" because of the way those DoCmd subroutines work.
Once the Access VBA environment is finished forming that string, it will pass it to the DB Engine, which probably in your case would be ACE, sitting there behind the scenes. In passing the string as an argument, it will strip out one layer of double-quotes. I have manually implemented the substitution to italicize what would have been presented. I did one other thing... I removed all of your .Value references because every one of them was superfluous. For anything that HAS a value, .Value is the default property.
This is what you would have passed to ACE.
Code:
UPDATE CUSTOMERS
SET
Firstname = '[I]Me.fname[/I]',
Lastname = '[I]Me.lname[/I]',
PostalAddress = '[I]Me.paddress[/I]',
City/District = '[I]Me.city[/I]',
ContactNumber = [I]Me.contact[/I] ,
RegistrationDate = #[I]Me.datej[/I]#,
Gender = '[I]Me.gende[/I]r',
DateofBirth = #[I]Me.datofbirt[/I]#,
MaritalStatus = '[I]Me.Mstatus[/I]',
Email = '[I]Me.Email[/I]',
CreditCardDigit = [I]Me.Ccrdnum[/I] ,
CreditCardExpDate = #[I]Me.ExpiryDate[/I]#
WHERE
CustomerID = '[I]Me.Cbodrop[/I]' ;
[CODE]
Your error PROBABLY came from the City/District line because nothing else there is wrong, as far as I can tell. In this context, you have a math operator (/} in the middle of the left-hand side of the assignment. This is why you don't use special characters in names. The fix, as has been noted by others, is to bracket that name. I see no other syntax errors. However, there is still a potential "gotcha" if ANY of the items enclosed in apostrophes (') could possibly CONTAIN an apostrophe or double-quote as legitimate data. Because that would unbalance the quoting in the statement and that would SURELY trigger an error.
.
What I did was took your first query from your first post and applied some simple rules. You had a DoCmd.RunSQL "statement" construct where the statement was fairly complex. However, doing it that way allows the Access environment one last chance to supply values for everything into the string. (This is actually a GOOD thing.) It does so because VBA syntax "hides" the fact that you DoCmd line is REALLY running something that internally looks like...
Code:
CALL DoCmd.RunSQL( SQLstatement, ...)
Since you have a complex string expression as the SQL statement, VBA is required by syntax rules to evaluate the expression and reduce it to a static string before passing it. All substitutions occur in the VBA environment because that is where that evaluation occurs. Normally, you don't think of it that way, but that is actually what is happening. VBA is just being "nice" about interpreting the statement. This implied CALL is what has to happen "behind the scenes" because of the way those DoCmd subroutines work.
Once the Access VBA environment is finished forming that string, it will pass it to the DB Engine, which probably in your case would be ACE, sitting there behind the scenes. In passing the string as an argument, it will strip out one layer of double-quotes. I have manually implemented the substitution to italicize what would have been presented. I did one other thing... I removed all of your .Value references because every one of them was superfluous. For anything that HAS a value, .Value is the default property.
This is what you would have passed to ACE.
Code:
UPDATE CUSTOMERS
SET
Firstname = '[I]Me.fname[/I]',
Lastname = '[I]Me.lname[/I]',
PostalAddress = '[I]Me.paddress[/I]',
City/District = '[I]Me.city[/I]',
ContactNumber = [I]Me.contact[/I] ,
RegistrationDate = #[I]Me.datej[/I]#,
Gender = '[I]Me.gende[/I]r',
DateofBirth = #[I]Me.datofbirt[/I]#,
MaritalStatus = '[I]Me.Mstatus[/I]',
Email = '[I]Me.Email[/I]',
CreditCardDigit = [I]Me.Ccrdnum[/I] ,
CreditCardExpDate = #[I]Me.ExpiryDate[/I]#
WHERE
CustomerID = '[I]Me.Cbodrop[/I]' ;
[CODE]
Your error PROBABLY came from the City/District line because nothing else there is wrong, as far as I can tell. In this context, you have a math operator (/} in the middle of the left-hand side of the assignment. This is why you don't use special characters in names. The fix, as has been noted by others, is to bracket that name. I see no other syntax errors. However, there is still a potential "gotcha" if ANY of the items enclosed in apostrophes (') could possibly CONTAIN an apostrophe or double-quote as legitimate data. Because that would unbalance the quoting in the statement and that would SURELY trigger an error.
.
Thanks very much .... i had done the [squarebracket] thing, earlier and i also removed the .Value. there was nothing wrong with the query syntax as u rightly said. the error was from a line directly beneath/after the query.
me.cusname.enabled =true
it had been commented out but became uncommented. and yeah i know that the syntax is rubbish .
i stripped the module down to the bare essentials and its now working fine. thanks a sir. and thanks to every body who assisted. @theDBguy@Gasman and everybody else
