Linking Forms (again)

[faceman]

I get an error that says: "Syntax error(missing operator) in query expression '[txtLineID] = 3PW4000ExMH0+00A-13+00' ".


What does this error mean?

(That long string is a concatenated field I am using as my PK)


Any help would be appreciated. The code I put in is as follows:


Private Sub cmdLaterals_Click()

On Error GoTo Err_cmdLaterals_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLaterals"
stLinkCriteria = "[txtLineID] =" & Me![MHLineID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdLaterals_Click:
Exit Sub

Err_cmdLaterals_Click:
MsgBox err.Description
Resume Exit_cmdLaterals_Click

End Sub

 

[charityg]

It looks like your MHLineID is a string value so try

stLinkCriteria = "[txtLineID] ='" & Me![MHLineID] & "'"

it's hard to see, but that's
doublequote [txtLineID] = singlequote doublequote & Me![MHLineID] & doublequote singlequote doublequote

 

[faceman]

Linking Forms

Thanks for the reply Charity, ubt when I tried the changes, it then prompts me with an input message box. I would rather not have to input that long string if I can avoid it.

 

[charityg]

I think I see what the problem is.

Me![MHLineID] should refer to a control named MHLineID on the form you are linking from

and txtLineID should be the name of a field in the recordsource from the form you are linking to.

Normally, you wouldn't name a field in your table txtLineID, so I'm assuming that portion isn't correct.

To make it simple, what is the name of the textbox on the form you are linking from? and what is the name of the field in the table that contains the data you are trying to find in the form you are linking to?

 

[faceman]

Forms Linking

Pat, Concatenating fields was not my preferred method, but I could think of no other way to uniquely identify the records. These records are for laterals on a sewer main. The sewer main is linked to a Project through a file number. The sewer mains can repeat themselves from project to project, so saying "A-1 to A-2" would not work. Even if I added stationing, it could still repeat. I can't just use the File number because I need to know which lines the laterals are on. So, if I have this:

Project: Whatever
FileNo : 3PW0000
Line: A-1 @ 4+00 to A-2 @ 5+00

My line Id is created when I update the last field(5+00). It stores this number in a field called MHLineID on the form and in the table. I have a button on the bottom of the form to open the Lateral form. The only problem I am having is it will not filter out just the laterals with that LineID. It will either give me the previously stated error, show all the laterals or open the prompt message box. The concatenated field is created before opening the lateral form, so it should not effect that. I have in the form_load to check for null value and if it finds a null, to copy the lineID from the line form. That parts works. Just the filter is not working for me.


Faceman

 

[faceman]

RE: Linking Forms

Oh well, screw the code. I still can not get VBA to filter out the records, but I attached a Macro to the button with the same control names and it filters them out perfectly.


Pat,
I understand that you are a senior member here and know alot about Access programming, but I am not a complete newbie with Access. I know about the dot syntax and have tried it as well. I know what my field names are and my control names(never leave them as default). Saying the same thing to me like I am a moron does not help me in the least. I don't mean to be rude, but I don't post here to be made to feel stupid or just to be typing. I came here for help with what the syntax error could mean. Apparently I was asking too much from you.

Thanks for nothing.




CharityG: Thanks for the sample database you sent, but it did not help either. For this instance, the subform method will not work well for me.
Your help was appreciated.


*Edit* Sorry, that was not Charity that sent the db, but Hayley. I appologize for the nixup.

Faceman

 

[Tay]

If I were you I'd apologise to Pat. I can't think of many people that spend as much time as she does trying to help others. Don't be so ungrateful.

Saying the same thing to me like I am a moron does not help me in the least

Perhaps you should try to not act like one.
Ps. I'm not trying to offend you - I'm just not particularly keen on your attitude.

 

[faceman]

Tay

Tay,
That was a real helpful post as well. Thanks for putting in your 2 cents worth. Why would I be grateful for nothing? I stated what my fields and controls names were. I have not changed them from my first post. I also know that you can get an input box to pop-up even when the control and field names are correct if you want it to. Pat was not even touching on my real question, which is: "What does that error message mean?". What operator is missing? Whay does this code work apparently for other people, but is not working for me on this?




Ps: For you to offend me, it would require me caring what you think of me in the first place. Just because I ask a question does not make me an idiot or a moron, only a person that wants to expand their knowledge.


Faceman

 

[faceman]

RE: Linking Forms

I still can not get VBA to filter out the records, but I attached a Macro to the button with the same control names and it filters them out perfectly.

Pat,
I gave up on the code for this as I was able to accomplish what I needed through a macro. Why the code was not working for me, I still have no idea. The names in the macro are the same as what I was trying in the code... so oh well. At least it is working on that part now and I can proceed with the rest of the database.

I really do not mean to be rude and I realize I do not have anything in my profile as to my level of experience with Access, but I get my hackles up when people automatically assume I am a big idiot. I guess it's just a character flaw on my part. Having nothing to do with Tay's attempted reproval, please accept my sincere appologies if I in turn offended you with my rudeness.


Faceman

 

[Hayley Baxter]

No Problem Faceman, only sorry the sample didn't help you much on this occasion. Sometimes posts and even emails between work colleagues can be read the wrong way to that of which the person meant them to read.

I'm just glad everyone seems to be happy again and at least you have something that works for you now

Hay

 

[Tay]

Faceman

I'm sorry my post wasn't of any use to you and that I was having a go. It's just that these Forums are extremely helpful, and a lot of people spend a lot of time helping others out. So when you post something as you did, saying "thanks for nothing", it irritates me somewhat, and such behaviour appears ungrateful.
Anyhow, glad you sorted your problem out (no thanks to me).