Re: Access VBA update recordset error

From: Brendan Reynolds (brenreyn)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 17:31:03 +0100

I still can't see the problem. The spaces in the field names should not, as
far as I can see, be a problem as long as you surround them with square
brackets, as you are doing. But here's something that might help to narrow
down the problem - try aliasing those field names in a SQL statement,
something like so ...

Dim strSQL As String
...
strSQL = "SELECT Roll, ReferenceRoll, LOCCD, BLDNO, UNIT, TENANT,
BusinessAddress, [Bus PC] AS BusPC etc. FROM tblbrzaccounts"
...
.Open strSQL, dbconn, , adLockOptimistic
...
!BusPC = txtPC
...

Also, it might help to know the exact text of the error message. 

-- 
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
"cityofgp" <cityofgp@discussions.microsoft.com> wrote in message 
news:60080A55-354E-482D-ADB0-DE901E87ED2F@microsoft.com...
> Here is the code that executes on the 'onclick' event.
> You will notice I have commented the fieldnames that have spaces because
> when I uncomment, I get the error message.
>
>   Dim rs As ADODB.Recordset
>    Dim dbconn As ADODB.Connection
>    Dim rate As Integer
>    Dim amount As Integer
>    Set dbconn = New ADODB.Connection
>    dbconn.ConnectionString = "DSN=BRZ;"
>    dbconn.Open
>
>    Set rs = New ADODB.Recordset
>    With rs
>        .Open "tblbrzaccounts", dbconn, , adLockOptimistic
>        .AddNew
>        !ROLL = txtRoll
>        !ReferenceRoll = txtRollRef
>        !LOCCD = txtLOCCD
>        !BLDNO = txtBLDNO
>        !UNIT = UNIT
>        !TENANT = txtTenant
>        ![BusinessAddress] = txtAddress
>        '![Bus PC] = txtPC
>        !TenantName = txtTenantName
>        !BusinessName = txtBusName
>        !MailingAddress1 = txtMailAdd1
>        !MailingAddress2 = txtMailAdd2
>        !City = txtCity
>        !Prov = txtProv
>        !MailingPC = txtBillPC
>        !Country = txtBillCountry
>        !BUSPHONE = txtWork
>        !BusinessRepName = txtBusRepName
>        !LessorsName = txtLessor
>        !LSSRPHONE = txtPhone
>        !Comments = txtComments
>        !CHANGESystem = txtChanges
>        !Start = txtStart
>        !End = txtEnd
>        '![Tentan TypeCode] = txtTenTypeCode
>        !Size = txtSize
>        !AssessmentCode = txtAssessmentCode
>        !BusinessType = txtBusType
>        '![Tax Status] = txtTaxStatus
>        '![Millrate Code] = txtMillrateCode
>        '![Current Assmt] = txtCurrentAssmt
>        ![2003TaxPrevAsmt] = txt2003PrevAssmt
>        !Status = txtStatus
>        '![Drive Code] = CStr(txtLOCCD & "-" & txtBLDNO & txtUnit & 
> txtTenant)
>        .Update
>    End With
>   rs.Close
>   dbconn.Close
>   MsgBox "saved"
>
> "Brendan Reynolds" wrote:
>
>> I think we'll need to see the actual code to trouble-shoot this one. The
>> full text of the error message might help, too.
>>
>> -- 
>> Brendan Reynolds (MVP)
>> http://brenreyn.blogspot.com
>>
>> The spammers and script-kiddies have succeeded in making it impossible 
>> for
>> me to use a real e-mail address in public newsgroups. E-mail replies to
>> this post will be deleted without being read. Any e-mail claiming to be
>> from brenreyn at indigo dot ie that is not digitally signed by me with a
>> GlobalSign digital certificate is a forgery and should be deleted without
>> being read. Follow-up questions should in general be posted to the
>> newsgroup, but if you have a good reason to send me e-mail, you'll find
>> a useable e-mail address at the URL above.

Relevant Pages

  • Re: default constraint
    ... GlobalSign digital certificate is a forgery and should be deleted without ... Dim cnn As ADODB.Connection ... >> The spammers and script-kiddies have succeeded in making it impossible ... >> me to use a real e-mail address in public newsgroups. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: default constraint
    ... GlobalSign digital certificate is a forgery and should be deleted without ... Dim cnn As ADODB.Connection ... >> The spammers and script-kiddies have succeeded in making it impossible ... >> me to use a real e-mail address in public newsgroups. ...
    (microsoft.public.vb.general.discussion)
  • Re: default constraint
    ... GlobalSign digital certificate is a forgery and should be deleted without ... Dim cnn As ADODB.Connection ... >> The spammers and script-kiddies have succeeded in making it impossible ... >> me to use a real e-mail address in public newsgroups. ...
    (microsoft.public.sqlserver.programming)
  • Re: default constraint
    ... GlobalSign digital certificate is a forgery and should be deleted without ... Dim cnn As ADODB.Connection ... >> The spammers and script-kiddies have succeeded in making it impossible ... >> me to use a real e-mail address in public newsgroups. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Limits to long interger field
    ... me to use a real e-mail address in public newsgroups. ... GlobalSign digital certificate is a forgery and should be deleted without ... >I am receiving an error message telling me I have entered too long a number ...
    (microsoft.public.access.gettingstarted)