Can't open form to correct record

[hcoburn]

Hi. I am new to this site and to database programming...

I have a form where I have placed a button, which when clicked, I want to open another form at the relevant record.

I'll try to explain this. The form which I am starting from called Case Details has a field in it called Customer which is not the primary ID field, but is pulled from the primary ID of a related table.

When I click on the button on this form, I need another form to open - Customer Details and I need it to open on the record for the current customer in Case Details. The matching field in Customer Details is called 'Company' and consequently I have the following code which isn't working...!

Private Sub CustomerDetails_Click()

DoCmd.OpenForm "Customer Details", acNormal, , "[Company]=" & Me![Customer]
End Sub

I have tried using code from similar threads on here and I have tried copying and amending macros that previously existed in the database but all to no avail, so I would be very grateful for any help.

Many Thanks,

Hazel.

 

[pbaldy]

It always helps when you define "isn't working". Error, unexpected result, etc. I note you said it had a field called Customer but your code uses Company.

 

[GaryPanic]

you need to define the following

case detail record and customer(client whatever) make sure that each has its PK set correctly and alos that the FK will make some sense


so case study 5 will equal customer 5 - if thats the way you have related these records

 

[hcoburn]

It always helps when you define "isn't working". Error, unexpected result, etc. I note you said it had a field called Customer but your code uses Company.

Of course - sorry! I get a runtime error 3634 'Data type mismatch in criteria expression'

Just in case it's useful, I have also used the code below which opens the form but at a blank record.

'Private Sub CustomerDetails_Click()

'DoCmd.OpenForm "Customer Details", acNormal, , "[Company]='" & [Forms]![Case Details]![Customer] & "'"

'End Sub

Thanks for taking a look,

Hazel.

 

[hcoburn]

you need to define the following

case detail record and customer(client whatever) make sure that each has its PK set correctly and alos that the FK will make some sense


so case study 5 will equal customer 5 - if thats the way you have related these records

Hi Gary,

Thanks for taking a look at my problem. Unfortunately, being totally new to this I have no idea what you mean by PK and FK.

Hazel.

 

[pbaldy]

What is the data type of [Company] in the table, and what value is contained in [Forms]![Case Details]![Customer]?

 

[hcoburn]

What is the data type of [Company] in the table, and what value is contained in [Forms]![Case Details]![Customer]?

Ahhhh - thank you! Just by asking that question and making me look at it, I have realised that Company is text, while the value in Customer is numeric. By changing [Company] to [ID] it has worked!

Thank you very much for your help! I can go to bed with a big smile on my face now

 

[pbaldy]

No problem; glad we got it sorted out.