Runtime error 13 - Type Mismatch

[access7]

Good Morning all

I was hoping someone may be able to give me a clue to why I am getting a type mis-match on the code attached to Frm_Search...
I am trying to get the Frm_Company to open at BOTH the correct Client / Prospect AND the correct Contact chosen from the listbox....

Any advice would be kindly welcomed???

Thanks in anticipation....

 

[MarkK]

This string is not property constructed.

"CompanyRef = " & Me!ListCompany And "Forms!Frm_Company!SubFrm_Contacts"

As a result your 'And' is being interpreted as a boolean operator between two and strings, which fails.
Cheers,
Mark

 

[access7]

Thanks for the response; I'm really scratching my head with this one. I understand that I am concatenating the string wrong and get your point about the 'And' operator... just struggling with how to fix it?? Do you have any tips on how to construct it properly...
I am really new to both Access and to VBA so I'm not asking for the code to be given to me as I like to make sure I'm understanding what it is I am doing but I've been trying different things now for about 3 hours and I'm sure its something really simple (usually is the smallest of things)! lol

 

[MarkK]

What are you trying to do? This is a filter condition opening a form, right? So it needs to be a string. And the second reference ...

Forms!Frm_Company!SubFrm_Contacts

... looks like it might just return a subform, which you can't really use here at all.
But to fix it it needs to do what you want it to do, so what do you want it to do?

 

[access7]

I would like the Frm_Company to open both with the CompanyRef and ContactRef chosen in the search box. At the moment if you pick a Client from the list (e.g. Back Office) then there are three contacts (Ian, Natalie and Ray) ... each of the contacts appears as a separate row in the list box on the search form - no matter which line you select at the moment the form always opens with the Contact details for Ian showing (as this is the first record) - I would like it to open showing the record for the Contact that has been selected on the previous form...
I hope that makes sense?? pelase let me know if you need further clarification??

 

[lcook1974]

lagbolt,
I beleive this is a new thread for this one.

http://www.access-programmers.co.uk/forums/showthread.php?t=213678

I've tried to help but dont' know how to reference the subform control based on the DoCmd.Openform command.

Larry

 

[access7]

Thanks Larry, and thanks again for your help before - I think that's the bit I'm struggling with too - how to reference the sub form. You are right though, same issue - I just thought I would post a new thread as the error I was getting was different...

 

[MarkK]

You can't use the filter condition opening the form because the data you are filtering for is on a subform. Right? The contact form is a subform.
What I would do is get the search form list to give me the ContactID, not the company. You can figure out the company using the contact, but you can't figure out the contact using the company.
So 1) get the search form to give you the contact info.
Then 2) I'd hand-over the ContactID to the company form and program the company form to find contacts on it's contact subform. Is this making sense?
Maybe you can use openargs for that, but maybe we should go one step at a time?
Cheers,
Mark

 

[access7]

Thanks Mark

You are right, the contact form is a sub form... it does make sense that you can find the company ref from the contact easier than the other way around.
I will have a look at changing it so that the search form gives me the Contact info instead... would that need to be changed at the query level (that populates the list box)?

 

[MarkK]

I don't know how the list box gets it's data.

Here's code I put on almost every meaningful form I produce for finding a record...

public sub gotoid(recordID as long)
  with me.recordsetclone
    .findfirst "recordID = " & recordID
    if not .nomatch then me.bookmark = .bookmark
  end with
end sub

So what you can on your company form is put a Public GoToContact method, which can then direct that request to the contact subform's Public Sub GoToID(ContactID as Long) method.
So then from your search form you ...
1) open the company form
2) call it's public gotocontact method
and the rest is handled by the company form.
Make sense?
Mark

 

[access7]

Hello again

Thanks for your time on this, it is really appreicated, being a self-taught VBA user I do struggle from time to time with certain things... the books and resources I have used have only taught me so much (considering I've only been doing it for a few months).
I do get the concept behind your last idea, and I am comfortable with creating and calling procedures ... only bit I dont understand is where you have used
with me.recordsetclone .findfirst "recordID = " & recordID if not .nomatch then me.bookmark = .bookmark end withThat is something I have never seen before... I have re-attached my database with some changes...
I have tried adding in a new variable (ContactRef) (in Module 1) that i was hoping would work in a similar way to the CompanyRef one... I have also put in a Public GoToContact procedure but I really dont know what to do with this one???

Sorry if I'm a complete noob! lol. Again, I do appreciate the advice!

 

[MarkK]

see if that makes sense...

 

[access7]

Wow, thanks for spending all that time looking into that for me... I have gone in to see how it works, I tried it on the Clients search and got an Invalid use of Null message - but I can see that it is working for the Prospects so I will follow that code and see what you have done, then perhaps I can replicate it for the Clients?
Some good weekend reading me thinks! Gosh, I have so much more to learn!
Many Thanks

 

[MarkK]

Yeah, you bet, all the best.
Mark