Referencing a Column with a Space in VBA (1 Viewer)

mhorner

Registered User.
Local time
Today, 10:50
Joined
May 24, 2018
Messages
50
Hi everybody,

Can anybody suggest how to reference a table column name that has a space in VBA?

I'm working on a function to duplicate a selected record and copy the desired data into the new record. All fields work except the fields with spaces in their name.

For example, the Certificate Number field in table tblCertificates, contains a space. I'm not sure the syntax to reference this field name in VBA. I've tried every syntax I can think of. Any guidance would be appreciated.


.AddNew
!Certificate Number = Me.txtCertificateNumber <------This line throws an error because of the space in the field name.
!Superceded = Me.chkSuperceded
!CertID = Me.cboCertID
!Issued = Me.txtIssued
!Market = Me.cboMarket
!Agency = Mecbo.Agency
.Update
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
Hi. Try enclosing the name with a space in square brackets. e.g. ![Certificate Number]

If you can, try to avoid using spaces in field names, though. Cheers!
 

mhorner

Registered User.
Local time
Today, 10:50
Joined
May 24, 2018
Messages
50
Hi, Thanks for your quick reply. But this unfortunately doesn't work. I receive the same error.

Thanks for the advice, but this is a DB I inherited. The author didn't exactly use good naming conventions. I'd like to try to update the names, but there so many references. I don't really know how to rename a field without breaking a critical functions.
 

mhorner

Registered User.
Local time
Today, 10:50
Joined
May 24, 2018
Messages
50
Interesting though, if I debug.print the field, it works fine...but if I try to push the field to a textbox, I receive an error that the field doesn't exist.

Debug.Print [Certificate Number] <-------This successfully prints the certificate number from the record into the debug window.
![Certificate Number] = Me.txtCertificateNumber <-------Object doesn't support this property or method.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
Interesting though, if I debug.print the field, it works fine...but if I try to push the field to a textbox, I receive an error that the field doesn't exist.

Debug.Print [Certificate Number] <-------This successfully prints the certificate number from the record into the debug window.
![Certificate Number] = Me.txtCertificateNumber <-------Object doesn't support this property or method.
Hi. Are you trying to store the value on a form into a table field?
 

mhorner

Registered User.
Local time
Today, 10:50
Joined
May 24, 2018
Messages
50
Hi DBGuy, I'm actually trying to use Allen Browne's duplicate record script.


I'm not entirely sure how to answer your question. I am just trying to create a duplicate record command button that copies fields and subtables into the new record. All fields copy into the new record perfectly, except the ones with spaces.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
![Certificate Number] = Me.txtCertificateNumber <-------Object doesn't support this property or method.
Okay, let's try to troubleshoot this one.

What do you get with this?
Code:
![Certificate Number] = "TEST"
 

mhorner

Registered User.
Local time
Today, 10:50
Joined
May 24, 2018
Messages
50
Ok, I feel like this is progress already :)

This did not throw an error. This successfully duplicate the record and populated "Test" into the certificate number field of the new record.
 

Isaac

Lifelong Learner
Local time
Today, 07:50
Joined
Mar 14, 2017
Messages
8,738
What happens if, while the code is in break mode, you try:

Debug.Print me.txtCertificateNumber

?

Edit: That's what I was thinking that maybe it has to do with the textbox value?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
Ok, I feel like this is progress already :)

This did not throw an error. This successfully duplicate the record and populated "Test" into the certificate number field of the new record.
Okay, so that sorted out half of the problematic line. Now, we'll have to figure out what's wrong with the other half. Are you sure the name of the Textbox is txtCertificateNumber? Maybe double check that. Just a thought...
 

mhorner

Registered User.
Local time
Today, 10:50
Joined
May 24, 2018
Messages
50
I see your thought process, and I like it.

So debug.print me.txtCertificateNumber throw the same error. I assure you, I am spelling it correctly. Maybe renaming the text box might help?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
I see your thought process, and I like it.

So debug.print me.txtCertificateNumber throw the same error. I assure you, I am spelling it correctly. Maybe renaming the text box might help?
Yeah, try that. It might help. You never know...
 

isladogs

MVP / VIP
Local time
Today, 14:50
Joined
Jan 14, 2017
Messages
18,186
what exactly is the error number/message?
 

mhorner

Registered User.
Local time
Today, 10:50
Joined
May 24, 2018
Messages
50
This actually resolved it. I renamed txtCertificateNumber to txtTEST and this actually worked. I changed the name of the textbox back to txtCertificateNumber and updated the code accordingly, and it functions perfectly.

I can't claim to know the inner workings of access terribly well, but maybe something disconnected or desynch'ed from the name. Renaming resolved this.

I really appreciate helping me through this one, do you have a patreon?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
This actually resolved it. I renamed txtCertificateNumber to txtTEST and this actually worked. I changed the name of the textbox back to txtCertificateNumber and updated the code accordingly, and it functions perfectly.

I can't claim to know the inner workings of access terribly well, but maybe something disconnected or desynch'ed from the name. Renaming resolved this.

I really appreciate helping me through this one, do you have a patreon?
Hi. Glad to hear you got it sorted out. However, just to complete the troubleshooting steps, try it again without the square brackets and see if you get an error. If you do, see if it's the same or different error than before. Cheers!
 

Isaac

Lifelong Learner
Local time
Today, 07:50
Joined
Mar 14, 2017
Messages
8,738
I see your thought process, and I like it.

So debug.print me.txtCertificateNumber throw the same error. I assure you, I am spelling it correctly. Maybe renaming the text box might help?
That was the underlying problem - something about the text box. I'm glad you got it resolved, although it seems like a sort of temporary glitch. (?)

PS ... I usually just use RecordsetObject .Fields("fieldname")
I've developed the habit, in cases where there are multiple ways of doing things all with their own set of background knowledge required, to generally go for the "one" (if it exists) that covers more contingencies--that way you have a fraction of things you have to memorize/remember. Very similar to the earlier discussion on ByRef/ByVal. You can either memorize the 5 interesting ways of doing it, or just use one that covers 4 of them, and another that covers 1 of them. The less default behaviors to memorize, the better. :)
 

mhorner

Registered User.
Local time
Today, 10:50
Joined
May 24, 2018
Messages
50
Thanks guys. Again, I really appreciate the help. I wish there was some way i could repay you.

DBGuy, I did remove the brackets and immediately got a compile error. I added the brackets back in and all functioned properly.

Thanks again!
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:50
Joined
Aug 11, 2003
Messages
11,696
Most likely its a disconnect in your eyes fooling your brain into thinking .... a small typo somewhere that you were unable to spot txtCertifcateNumber
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
Thanks guys. Again, I really appreciate the help. I wish there was some way i could repay you.

DBGuy, I did remove the brackets and immediately got a compile error. I added the brackets back in and all functioned properly.

Thanks again!
Hi. Glad to hear you were able to confirm the solution. Good luck with your project.
 

Isaac

Lifelong Learner
Local time
Today, 07:50
Joined
Mar 14, 2017
Messages
8,738
Thanks guys. Again, I really appreciate the help. I wish there was some way i could repay you.

DBGuy, I did remove the brackets and immediately got a compile error. I added the brackets back in and all functioned properly.

Thanks again!
Yes, it will never compile with a space there (unless you use the .Fields() method, which also gives you Intellisense which is nice).
Using the Immediate Window is another thing I would recommend getting familiar with. Similar to Debug.Print, except, while the code is in Break mode, you can ask the Immediate window any number of questions (questions you hadn't previously planned to ask with debug.print code), as much as you like. This would have shown you both errors - the space, and the textbox. Very handy.
 

Users who are viewing this thread

Top Bottom