Button from subform opens wrong record on other form

[aian]

Hello everyone,

I have a form named SearchResults and a subform named SearchResultsSecondary.

Inside the SearchResultsSecondary, I have a button that, when clicked, runs a macro named pass2form.

This macro, is supposed to open a form named CHARGES in edit mode, filtered by a query named qrypass2form.

This query is supposed to pass the current CLIENT_ID from the SearchResultsSecondary form and the today's day (through the Date() function) to the CHARGES form, so as to allow me to edit other fields on the specific filtered record.

The problem is that when I click the button, a record DOES open in the CHARGES form, in the correct date but in the wrong CLIENT_ID, therefore in the wrong client.

What in God's great earth am I doing wrong? I have checked everything again and again but with no luck. I even deleted all my clients and entered new ones (dummy entries) to check it again but I'm having the same results.

Please note that I chose the method with the macro because my VBA knowledge is rather poor.

Thanks in advance, all your help is highly appreciated.

Alexander

 

[RuralGuy]

For those of us that are macro challenged, how about going to Tools>Macro>Convert Macro to Visual Basic and convert that macro. Then post the converted code and maybe we can determine what is happening for you.

 

[aian]

Thanks for your reply, RuralGuy. Well, here's the code...

Option Compare Database

'------------------------------------------------------------
' PASS_FORM_ID_AND_TODAY_S_DATE
'
'------------------------------------------------------------
Function PASS_FORM_ID_AND_TODAY_S_DATE()
On Error GoTo PASS_FORM_ID_AND_TODAY_S_DATE_Err

DoCmd.OpenForm "frm_CHARGES", acNormal, "qry_PASS FORM_ID AND TODAY'S DATE", "", acEdit, acNormal


PASS_FORM_ID_AND_TODAY_S_DATE_Exit:
Exit Function

PASS_FORM_ID_AND_TODAY_S_DATE_Err:
MsgBox Error$
Resume PASS_FORM_ID_AND_TODAY_S_DATE_Exit

End Function


And I can't understand a single word of it all... (

 

[RuralGuy]

That helps! Your query makes no sense at all. Is your frm_CHARGES bound to a query? If so then all you need to do is specify a WhereCondition and it should work.

 

[aian]

It's bound to a query that takes the CLIENT_ID from the subform SearchResultsSecondary and the today's day (through the Date() function) and is supposed to find the appropriate record (from the CHARGES form). Isn't that correct? Shouldn't it be returning the correct result?

 

[RuralGuy]

What do you have in the RecordSource of your report?

 

[aian]

Report? What report? I only want the CHARGES form to have the CLIENTID and today's date automaically inserted by the SearchResultsSecondary form and the query.

I don't understand your question, RuralGuy, should I have a report? What for?

 

[RuralGuy]

Sorry about that. I meant the RecordSource of the CHARGES form.

 

[aian]

The RecordSource of the CHARGES from is a query named qryDATES. This query has only 2 fields, CLIENT_ID and DATE, and it finds these records from an APPOINTMENTS table. Let me also mention that I have set it up so that it shows only excluive records (if that is useful to you).

So, at first the user adds an appointment (let's say he added it three days ago) and today, when he finishes meeting with the client, he enters a Search form, writes the surname of the client, and the SearchResults form shows a SearchResultsSecondary subform with all the customers with that surname.

Then, there is a button next to each surname that is supposed to open the CHARGES form, exactly at that specific client, with the current date (hence Date()).

It is set up correctly, isn't it RuralGuy? Why doesn't the CHARGES form get the correct CLIENT_ID from the SearchResultsSecondary subform? It returns some other client (random client). Let's say I search for the surname SMITH. I find SMITH, click on his button and then the CHARGES form opens with the correct date (today) but with client name being JONES!

If I search for Mr ADAMS, the CHARGES form opens with Mr MACARTHY. Then, if I search again for SMITH, it returns again with JONES! Grrrrrr...

I tried to erase all clients and made just 3 new ones: Mr 11111, Mr 22222 and Mr 33333. Then the bloody CHARGES form opened without ANY name!

In the CHARGES form, I initially had only the FullName field (combo box). This takes surnames from a query that includes CLIENT_ID, Name and Surname and it creates a new field named FullName. I then included a CLIENT_ID text box to see if the CHARGES form receives the correct CLIENT_ID from the SearchResultsSecondary subform. And it does. But the combo box with the Surname on it, DEOSN'T show the correct client... Grrrrrrrr again...

By the way, the RecordSource for the CLIENT_ID field in the CHARGES form is:

SELECT FULLNAMES.CLIENT_ID, FULLNAMES.FullName FROM FULLNAMES ORDER BY FULLNAMES.FullName;

Do I have to visit a shrink, RuralGuy?

 

[RuralGuy]

I hope you are not using lookup fields in any of your tables. What do you get when you change your macro to simply:
DoCmd.OpenForm "CHARGES"

 

[aian]

I am using lookup fields in most tables. But as I read in what you sent me it's rather bad... I'll just remake everything without lookup fields (obviously this needn't be changed in all my forms, right?). I'll try and revert to you as soon as possible...

When I changed my macro to what you said, the CHARGES form opens and both the CLIENT_ID and DATE fields said #Name; (I'm not sure about the english version of the error since I'm using a Greek language version...)

 

[RuralGuy]

I would expect the results of opening the form with the new macro to be the same as opening the form directly from the database window. Lookup fields really do just confuse the issue when creating forms.

 

[aian]

OK, I did what you said, I changed all the lookup fields in all my tables.

I also rebuild from scratch the CHARGES form. I did this through the access wizard, took only two fields (CLIENT_ID and DATE), and then I inserted a subform and bound the two (form & subform) via the CHARGE_ID field. You were totally correct, now things seem to have improved, but not yet completely.

Now, the CHARGES form accepts the correct CLIENT_ID from the SearchResultsSecondary subform, but when I try to enter ANYTHING in the subform (in the fields you see circled with blue color in the photo below), there comes a message saying (in free translation, but I hope you'll understand much more than I did):

You cannot give a value to this object
* Object might be a control element in a form which is read only
* Object might be in a form opened in design view
* The value might have a large size for this field

This message really troubles me a lot. The first option (read ony form) is out of the question. The second one isn't true as nothing else was opened at the moment, and the third is paranoid, since the PRODUCT_ID field takes data from the PRODUCTS table via a combo box, it isn't something tha I type).

The CHARGES form gives values to a table that includes the following fields:

CHARGE_ID
CLIENT_ID-------|______These two are combined primary fields
DATE------------|
PRODUCT_ID
AMOUNT

The two columns you see circled with blue color give values to the PRODUCT_ID and the AMOUNT fields. In the form, I have hidden the CLIENT_ID and the CHARGE_ID fields (not important to be viewed from the form user).

I also would like to ask you if there's any way to automatically show the client's FullName (Surname+Name as stored in the qryFullName) in the RED rectangle.

Thank you for your patience, I really appreciate all your efforts RG...

 

[RuralGuy]

While I'm studying your post, here's another link to put in your favorites.
Problem names and reserved words in Access
Date is a reserved word!

 

[RuralGuy]

Assuming the Charges form is bound to a query, can you open that query directly and make changes? It might be non-updateable. It would also be possible to display the client's full name. The difficulty would depend on how you have constructed your tables. In spite of the language difference, can you post a sample of your db that we can look at? It might make it easier to offer solutions. Then again, it might make it tougher.

 

[aian]

Sure RG, no problem at all...

In the meantime though, I removed the query to which the CHARGES form was bound...

But as far as I remember, it WAS updateable

Please ignore the initial menu, it is far from completed yet and it is there just to remind me to fix it when done...

Thank you

P.S. 1 For size reasons, I compressed the DB in RAR format, then renamed it to zip... Hope you have an Unrar program... ;o)
P.S. 2 The DATE field was a translation, as you'll see. But your link is amazing and brand new to me. Thanks again...

 

[RuralGuy]

I can UnRAR but does your db compile? I'm getting a corrupt db message.

 

[aian]

It does compile, but you can download it from:

http://www.iliasfragiskatos.gr/irene.zip

It's a regular ZIP file, no need to rename it

 

[RuralGuy]

What version of Access are you using? I get the corruption message whenever I try and view any of your code.

 

[RuralGuy]

Or look at any queries.