Single and double quotes (1 Viewer)

hogdriver53 · Jan 1, 2020

[SOLVED]Single and double quotes

Hi all,

I've searched high and low all over the Net, but am getting stumped .... And I think the culprit is/are the quotation marks. I've downloaded and modified a code I found on the net and it works to a point ....

I use Access 2016.

I have a db, where I would like to open and populate a form (frmWorksheet) with a record that is displayed on a report (rptMDEMexams), which itself is based on a query (qryMDEMexams), which is based on a table (tblMDEMexams). I know it seems over the top, but being a newb this makes is easiest for me to keep it sane

I have a 'Print' button on the report (each line has it's own print button) and when I press the button, it opens the 'Worksheet' form, but pre-populates it with the first record from the table.

Based on the searching and reading I've done is that it all comes down to the quotation marks.

As per my code below, the first double quote is fine but the single quote and double quotes after ... Me![txtContainerNumber]... appear to be the culprits. The single/double quotes turn green, which to me means it's an info line and not used/displayed (as in "blocked" DoCmd at the top of the code).

---------------------------------------

Code:

Private Sub cmdPrint_Click()


'DoCmd.OpenForm "frmRecord", , acNormal, , "FIELD = " & Me!FIELD
    
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmWorksheet"
    stLinkCriteria = "[txtContainerN]= '" & "Me![txtContainerNumber] " [B][COLOR="Red"]'"[/COLOR][/B]
    
    DoCmd.OpenForm stDocName, , stLinkCriteria

End Sub

---------------------------------------------

No matter what I try and the order of quotes and single/double quotes, I can't get it to work, and I get the same result.

What am I doing wrong? It's driving me crazy ....

isladogs · Jan 1, 2020

Hi
Your two identical posts were moderated.
This often happens with new users if the post contains code and/or is quite long.
If it happens, one of us will approve the post as soon as we notice it.
So please don't just redo it.
Anyway, I've added code tags (# button on toolbar) and I deleted the other post ...

vba_php · Jan 1, 2020

Code:

 stLinkCriteria = "[txtContainerN]= '" & Me![txtContainerNumber]  & "'"

but if that doesn't work, try:

Code:

 stLinkCriteria = "[txtContainerN]= " & Me![txtContainerNumber]

also, opening a form AFTER you've already opened a report is pretty rare I would say. actually, I don't think I've ever seen it done. usually it goes:

click button on form
report opens
hit print report button
close report to go back to form

by the way Andrew (that is you, right?), welcome to your first experience with a vast array of different viewpoints. That's what makes this place so entertaining.

theDBguy · Jan 1, 2020

Hi. Just a guess but try it this way:

Code:

 stLinkCriteria = "[txtContainerN]= '" & Me![txtContainerNumber] & "[B]'"[/B]

Edit: Oops, looks like I was a little slow... Sorry for the duplicate info...

isladogs · Jan 1, 2020

And another version...
1. There's a comma missing in the OpenForm line
2. Assuming its a text field (despite the name) then

Code:

    stDocName = "frmWorksheet"
    stLinkCriteria = "txtContainerN= '" & Me.txtContainerNumber & "'"
    DoCmd.OpenForm stDocName, ,[B][COLOR="Red"] ,[/COLOR][/B]stLinkCriteria

or just do it in one line

Code:

 DoCmd.OpenForm "frmWorksheet", , [COLOR="red"][B],[/B][/COLOR]"txtContainerN= '" & Me.txtContainerNumber & "'"

3. But if its a number field then

Code:

 DoCmd.OpenForm "frmWorksheet", ,[B][COLOR="red"] ,[/COLOR][/B]"txtContainerN= " & Me.txtContainerNumber

Note that the square brackets were superfluous and it should be Me. instead of Me! (though both will work)

vba_php · Jan 1, 2020

isladogs said:
it should be Me. instead of Me! (though both will work)

I'm still not convinced of this. Will the confusion of the differing symbolisms in access ever be solved I wonder? sometimes it works, sometimes it doesn't, version to version, scenario to scenario....the mishaps are plentiful IMO.

hogdriver53 · Jan 1, 2020

isladogs said:
Hi
Your two identical posts were moderated.
This often happens with new users if the post contains code and/or is quite long.
If it happens, one of us will approve the post as soon as we notice it.
So please don't just redo it.
Anyway, I've added code tags (# button on toolbar) and I deleted the other post ...

Awesome. Thank you. When I posted first time, a message flashed but I didn't see what it said. Now I know.

vba_php · Jan 1, 2020

theDBguy said:
Edit: Oops, looks like I was a little slow... Sorry for the duplicate info...

you're a funny guy, dbGuy!

theDBguy · Jan 1, 2020

vba_php said:
you're a funny guy, dbGuy!

Just trying to say I didn't mean to step on anyone's toes. That's all. Cheers!

hogdriver53 · Jan 1, 2020

vba_php said:
Code:

stLinkCriteria = "[txtContainerN]= '" & Me![txtContainerNumber] & "'"

but if that doesn't work, try:

Code:

stLinkCriteria = "[txtContainerN]= " & Me![txtContainerNumber]

also, opening a form AFTER you've already opened a report is pretty rare I would say. actually, I don't think I've ever seen it done. usually it goes:

click button on form

report opens

hit print report button

close report to go back to form

by the way Andrew (that is you, right?), welcome to your first experience with a vast array of different viewpoints. That's what makes this place so entertaining.

Thank you, Adam, for your reply.
The reason I need it the way I have is to see the report of all the containers and then from there I can pick which worksheet to print with which container.

I've looked through AW forums in the past and I find them very helpful. I got a lot of good/different ides for some of the things I incorporated into the db. Variety is what makes it so cool - seeing different ideas and then using what works the best

vba_php · Jan 1, 2020

hogdriver53 said:
Variety is what makes it so cool - seeing different ideas and then using what works the best

we assume this issue is solved then? If so, mark the thread as such if you would please. I'm glad you find this place helpful. There's no doubt that we kick a serious amount of a$$ around here.

theDBguy said:
Just trying to say I didn't mean to step on anyone's toes. That's all. Cheers!

I think that's inevitable, guy. As long as we don't twist the question asker's brain into a pretzel by bombarding him/her with too many responses, I don't see anything wrong with more than 1 person throwing coins in the fountain.

hogdriver53 · Jan 1, 2020

Thank you, Colin and Adam, for your suggestions.
I tried all the different ways that you've suggested, but still not working (all on one line, and as I had it in the OP).
The txtContainerN and txtContainerNUmber fields are both text fields.

I noticed that when I added the 3rd comma in the DoCom line, it put a space after it and there was a space between it and stLinkCriteria. In your examples, there is no space. No matter what I tried, as soon as I clicked away from that line, the space was added automatically.
When I only had the 2 commas, it would work, but still going to the first record in the table.
When I left the 3rd comma in and the space after it, a box pops up and asks for the container number (txtContainerN).

vba_php · Jan 1, 2020

hogdriver53 said:
as soon as I clicked away from that line, the space was added automatically.

the vba editor's intelligence causes that stuff.

hogdriver53 said:
When I only had the 2 commas, it would work, but still going to the first record in the table. When I left the 3rd comma in and the space after it, a box pops up and asks for the container number (txtContainerN).

this might be a case where you have to upload your file so these guys can have a look. Can you do that? If sensitive data, ditch it and add a small amount of fake data so you don't give away any trade secrets, etc...

theDBguy · Jan 1, 2020

Hi. Are you able to post a sample copy of your db? You should be able to attach a file if you zip it up first.

Micron · Jan 1, 2020

Will the confusion of the differing symbolisms in access ever be solved I wonder?

Not as long as there's lack of understanding and/or retention of knowledge learned. It sure takes a lot of time.
Regarding ! :
It provides late bound access to the default member of an object by passing the literal that follows the ! as a string argument for that member. AFAIK, the only time you must use ! over . is when dealing with recordsets and certain object collections; e.g.

Code:

With rs
  .Edit
  !field1 = Me.txtbox1
  !field2 = Me.txtbox2
etc

and Forms!frmMain

If you use Me! and misspell the name of the default member as in
Me!txtFnme when it should be txtFname then the error will not be found on compile, only at run time - because the reference is LATE bound. I never use Me! for that reason alone.

hogdriver53 · Jan 1, 2020

Hi guys,

I had to delete the previous message with the attachment, because I had to remove some more stuff from the db.

This one is OK to go out to the world. I removed some sensitive data.

Thanks for all your help.

Andrew

theDBguy · Jan 1, 2020

hogdriver53 said:
Hi guys,

I had to delete the previous message with the attachment, because I had to remove some more stuff from the db.

This one is OK to go out to the world. I removed some sensitive data.

Thanks for all your help.

Andrew

Hi Andrew. Can you please re-add the instructions you had included earlier? Thanks.

theDBguy · Jan 1, 2020

Okay, I took a look and hope the attach works for you.

isladogs · Jan 1, 2020

I had a look too. Once I realised the field & control names originally supplied by the OP were wrong, this also worked

Code:

   DoCmd.OpenForm "frmWorksheet", , , "[Container Number] = '" & Me.[Container Number] & "'"

as did this:

Code:

DoCmd.OpenForm "frmWorksheet", , , "[Container Number] = '" & Me.txtContainerN & "'"

vba_php · Jan 1, 2020

Hey andrew, when you get this thing sorted out can you answer my question bout joey cramer? See here: https://www.access-programmers.co.uk/forums/showpost.php?p=1659906&postcount=6

Single and double quotes (1 Viewer)

Registered User.

MVP / VIP

Forum Troll

I’m here to help

MVP / VIP

Forum Troll

Registered User.

Forum Troll

I’m here to help

Registered User.

Forum Troll

Registered User.

Forum Troll

I’m here to help

AWF VIP

Registered User.

Attachments

I’m here to help

I’m here to help

Attachments

MVP / VIP

Forum Troll

Similar threads

Users who are viewing this thread