Syntax with ! or equivalent please

[Gasman]

Hi guys,

This morning I thought I'd investigate disconnected recordsets as it was recommended to me a good while back when I created a db for work colleagues, but did not have the time to investigate any further.

I discovered a site this morning that gave a good example using the Northwind DB which I have in my current workplace, so I could fiddle with that in my own time.

I cannot find the link any more but the code looks like below. My changes are in red.

 Private Sub cboCompanyName_AfterUpdate()
'Populate form with filtered results.


On Error GoTo ErrHandler


Dim strCriteria As String


strCriteria = "ID = '" & Me!cboCompanyName.Value & "'"
' strCriteria = "CustomerID = " & Me!cboCompanyName.Value

Debug.Print strCriteria


With CustomerDisconnectedRS


If Not (.EOF) Then


.Find strCriteria


Me.txtCustomerID = !ID


[COLOR="Red"]Me.txtContactName = ![Last Name]

Me.txtContactTitle = [Job Title][/COLOR]


Me.txtAddress = !Address


Me.txtCity = !City


'Me.txtRegion = !Region


'Me.txtCountry = !Country


'Me.txtPhone = !Phone


'Me.txtFax = !Fax


End If


End With


Exit Sub

However the original code was using fields with no spaces and were also incorrect. This is the code behind the save button, that I have only commented out for now

Private Sub cmdSave_Click()
On Error GoTo ErrHandler:


With CustomerDisconnectedRS


'!ID = Me.txtCustomerID


!ContactName = Me.txtContactName


!ContactTitle = Me.txtContactTitle


!Address = Me.txtAddress


!City = Me.txtCity


'!Region = Me.txtRegion


'!Country = Me.txtCountry


'!Phone = Me.txtPhone


'!Fax = Me.txtFax


End With


cmdUpdateServer.Enabled = True


Exit Sub


ErrHandler:


MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"


End Sub

What I am trying to find out is the syntax I must use when the filenames have spaces as the Northwind db currently has?

I've tried ![Last Name], !"Last Name", "!Last Name", !["Last Name"] with no success.

What is the correct syntax for using the ! with field names that contain spaces?, or a better way to reference them.

TIA

 

[jdraw]

You may get some ideas/insight from these links

http://www.access-programmers.co.uk/forums/showpost.php?p=1413594&postcount=3

http://www.access-programmers.co.uk/forums/showthread.php?p=1332329

https://support.microsoft.com/en-us/kb/184397

 

[James Deckert]

Have you made sure the fields you're trying to refer to are in your recordset?
usually rs![Last Name] is the right format or rs("Last Name")

 

[Cronk]

We are of course assuming that the recordset is defined at least at the module level and is opened somewhere else before calling the procedure.

 

[Gasman]

Ok, I found the link by googling for CustomerDisconnectedRS. (Thank you google)

That brought up the site I got the code from
http://www.techrepublic.com/blog/ho...over-a-network-using-disconnected-recordsets/

and this thread.

James, yes that is my problem. Northwind appears to have changed the field names since the article was written.

Cronk, Yes the recordset has been defined, it picks up the ID field no problem.

I will try again tomorrow, using those links. Thank you jdraw

I am home now and only have 2003. Work has 2013 and indeed the field names have changed over the years for the Customer table, yet they still have spaces in them?

If still unsuccessful I will post all the code, but I have only changed the names for the data to match the 2013 Customer table, or at least tried.

EG 2003 ID field is called Customer ID, but I am pretty sure the 2013 version only has ID ?

Edit: I can see where I have gone a little wrong. My 2003 Customer db has field names matching the code from the site, but spaces in the captions.
I am pretty sure I was looking in the right place in the 2013 version, but perhaps not.

Anyway learning how to use any fieldnames with spaces will prove worthwhile.

We will see tomorrow.

Thank you for all the replies.

 

[gemma-the-husky]

this is correct

rst![Last Name]

you can also use

rst.fields("last name")

if these do not work, there is something else amiss.

 

[Gasman]

this is correct

rst![Last Name]

you can also use

rst.fields("last name")

if these do not work, there is something else amiss.

Thanks Dave,

The code is using With CustomerDisconnectedRS and then !ID so I was not using the recordset name as I was within the With statement block.

I will look carefully at the table tomorrow and use what has been posted.

 

[jdraw]

I googled disconnected ado recordset northwind.
I got this link
https://books.google.ca/books?id=pj...q=disconnected ado recordset northwind&f=true

which is an entire chapter 16 from a book.

Good luck.

 

[Gasman]

I googled disconnected ado recordset northwind.
I got this link
https://books.google.ca/books?id=pj...q=disconnected ado recordset northwind&f=true

which is an entire chapter 16 from a book.

Good luck.

Thank you jdraw, I get an error message that it either does not exist or I've reached my vewing limit for the book.

I'll play with what I have tomorrow at work and see how I get on.

Edit:

Found that chapter by using the same search criteria in Google. There must have been something unique to you in that link?

 

[jdraw]

Here is the search I made and the results.
I picked the first, but I'm sure the second is similar.

 

[Gasman]

Well I came into work this morning and the 2013 fields in Northwind are named differently, it is not just the captions, in fact the Customer table does not seem to have any captions.

I amended my code to ![field name] and got an error about trying to use a macro not available. That happened yesterday and so I added MS activex data objects 2.1 library which allowed the disconnected functions to run, but then I fell over at the syntax.

That was still selected(works has tendency to clean up after you here, no favourites allowed in Chrome etc). So i went looking for other libraries, found version 6, and added that instead. Now the syntax works. Yeh.

However I get an error '-2147217887: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.' when I try and Update Server via the button.

The backend is on my network drive. I put my front end in my documents on the C drive. I can retrieve the data OK and it shows on the form.

I am off googling for the error, but if anyone can spot the reason I'd be very grateful.

The whole code is below.

Option Compare Database
Option Explicit
Dim CustomerDisconnectedRS As ADODB.Recordset
 
Private Sub cboCompanyName_AfterUpdate()
'Populate form with filtered results.

On Error GoTo ErrHandler

Dim strCriteria As String

strCriteria = "ID = '" & Me!cboCompanyName.Value & "'"
' strCriteria = "CustomerID = " & Me!cboCompanyName.Value
Debug.Print strCriteria

With CustomerDisconnectedRS

If Not (.EOF) Then

.Find strCriteria

Me.txtCustomerID = !ID

Me.txtContactName = ![Last name]
Me.txtContactTitle = ![Job Title]

Me.txtAddress = !Address

Me.txtCity = !City

'Me.txtRegion = !Region

'Me.txtCountry = !Country

'Me.txtPhone = !Phone

'Me.txtFax = !Fax

End If

End With

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub
Private Sub cmdSave_Click()
On Error GoTo ErrHandler:

With CustomerDisconnectedRS

'!ID = Me.txtCustomerID

![Last name] = Me.txtContactName

![Job Title] = Me.txtContactTitle

!Address = Me.txtAddress

!City = Me.txtCity

'!Region = Me.txtRegion

'!Country = Me.txtCountry

'!Phone = Me.txtPhone

'!Fax = Me.txtFax

End With

cmdUpdateServer.Enabled = True

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub
Private Sub cmdUpdateServer_Click()
Dim cnn As New ADODB.Connection

On Error GoTo ErrHandler

'—————

'Example connection is to active connection.

'Set connection here for foreign or networked data.

'—————

Set cnn = Application.CurrentProject.Connection

With CustomerDisconnectedRS

.ActiveConnection = cnn

.UpdateBatch

End With

cmdUpdateServer.Enabled = False

Exit Sub

ErrHandler:
Debug.Print Err.Number & ": " & Err.Description
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub
Private Sub Form_Close()
Set CustomerDisconnectedRS = Nothing

End Sub
Private Sub Form_Open(Cancel As Integer)
'Populate disconnected recordset object with customer information.

'Populate cboCompanyName with CustomerID and CompanyName.

Dim cnn As ADODB.Connection

Dim strSQL

On Error GoTo ErrHandler

Set CustomerDisconnectedRS = New ADODB.Recordset

'—————

'Example connection is to active connection.

'Set connection here for foreign or networked data.

'—————

Set cnn = Application.CurrentProject.Connection

strSQL = "SELECT * FROM Customers"

With CustomerDisconnectedRS

.CursorLocation = adUseClient

.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic

'Disconnect.

Set cnn = Nothing

End With

'Populate combo box.

Set cboCompanyName.Recordset = CustomerDisconnectedRS

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub

 

[Gasman]

I've come home and created the form in 2003. Apart from one error about duplicate data, and only the CustomerID is indexed no dupes, it all works fine. I used the 6.0 library.

So I at least have the basics working, which was all I was really after as I have no project as yet to make use of it. Just for self education.

Thanks for all the replies.