Table or Form problem

[booboo]

I have two tables:
CustInfo (main table with all customer info)
CustPay (History table for payments received)

They are linked as from Custinfo by field TblCustPay Lookup to TblCustPay field ID (autonumber)

I made a query with the fields from CustInfo :
ID
Name
Addr
Lst_Pay
Bal_Amt

and with fields from CustPay:
ChkNum (need appended to custpay tbl)
ChkAmt (need appended to custpay tbl)
RecDate (need appended to custpay tbl AND updated to Lst_Pay in custinfo tbl)
Then I need to update Bal_Amt (custinfo tbl) minus ChkAmt from the input value

Then I made a form with all these fields from the query
Can navigate through the records from Custinfo but nothing changes in the fields from CustPay.
Also, I entered data and it placed it in the first record of CustPay tbl the first time only.

I have tried various samples of code editing and editing.
I now have a migraine trying to figure this out.

Any guidance is greatly appreciated.

[NeoPa]

My first thought would be to check if it's an updatable query. If not - that could cause you various problems.
If you find it is, it might then help if we had your SQL to look at for the query.

[booboo]

The query the form is built from ? It is set as update type.
I believe I need a query or procedure that takes the form data and puts it in the tables. Correct ?
If so, that is what I don't have and need some guidance on what is involved in it. I think. But can one query / do all I need done, I suppose as an after_event ?code

[NeoPa]

...
Then I made a form with all these fields from the query
...

What you say makes little sense to me.
You don't seem to have made any intelligible response to my post so I have no way to proceed with this.

[booboo]

I did a query based on the tables and selected only the above fields from each table in the query. Then I built a form based entirely on that query.
I don't know how to explain it any other way.

Apparently, just building a form from a query built from tables is not enough for the changes / inputs made in the form to do anything with the existing tables ?

[NeoPa]

My first thought would be to check if it's an updatable query. If not - that could cause you various problems.
If you find it is, it might then help if we had your SQL to look at for the query.

Is it an updatable query?
If it is post your SQL.

Answer question then follow instruction.
NB. Don't expect me to follow you on your tangents if you can't respond to my post.

[booboo]

Update query =
 
INSERT INTO TblPayInfo ( ID, CHKNUM, RECDATE, CHKAMT )
SELECT TblCustInfo.ID, TblPayInfo.CHKNUM, TblPayInfo.RECDATE, TblPayInfo.CHKAMT
FROM TblPayInfo INNER JOIN TblCustInfo ON TblPayInfo.ID = TblCustInfo.TblPayInfo_ID;

Append query =
 
UPDATE TblPayInfo INNER JOIN TblCustInfo ON TblPayInfo.ID = TblCustInfo.TblPayInfo_ID SET
SET TblCustInfo.LST_PAY = [RECDATE], TblCustInfo.BAL_AMT = [Bal_Amt]-[CHKAMT];

[booboo]

Assuming these are correct and will do what I need, is there a way to combine the two to do both in the same routine then reset the form for the next record ?

[NeoPa]

Is it an updatable query?
If it is post your SQL.

Answer question then follow instruction.
NB. Don't expect me to follow you on your tangents if you can't respond to my post.

Is this an answer to "Is it an updatable query?" Only indirectly. I can guess it's not, as you have posted two non-SELECT queries.

As it's not - why have you posted the SQL?

Please note my earlier NB. You're not responding to my question but going off at tangents again. I would like to help, but it's increasingly difficult if you don't opay any attention to what I'm saying.

Let's start again.

Your first post mentions building a form on a query where two tables are connected. This is the query I'm talking about. I need to understand what's what from that perspective.

[booboo]

Ok, I made a query, which is a SELECT query just to build a form with. (Since someone told me, on here I believe), that all forms should be built from a query.

This is that query:

SELECT TblCustInfo.ID, TblCustInfo.NAME, TblCustInfo.ADDR, TblCustInfo.LST_PAY, TblCustInfo.BAL_AMT, TblPayInfo.CHKNUM, TblPayInfo.RECDATE, TblPayInfo.CHKAMT
FROM TblPayInfo INNER JOIN TblCustInfo ON TblPayInfo.ID = TblCustInfo.TblPayInfo_ID
ORDER BY TblCustInfo.NAME;

[NeoPa]

Well, we're getting somewhere now. Still a fair way to go before we have the question though.
Is it updatable? (I think I've asked this one a few times already.)

Your original post suggests that the two tables are linked in the query, but it's unclear what it means (what it actually says can't be true - guessing what it means is harder).

I have two tables:
CustInfo (main table with all customer info)
CustPay (History table for payments received)

They are linked as from Custinfo by field TblCustPay Lookup to TblCustPay field ID (autonumber)
...

The reason this is important is that I can't see that the two tables (from their names) can sensibly link in any other way than CustInfo (One) to CustPay (Many). Oh gracious. I've just checked and the table names aren't even the same ones. How can I make any sense of this?
Assuming then, that what you refer to as CustInfo is actually TblCustInfo and the same with CustPay and TblPayInfo, how can TblCustInfo have a TblPayInfo_ID field?

I know there are a number of questions in here, principally because you've ignored the questions from previous posts. I should explain that if the questions are not all answered this time, or an explanation given for why, I will give up on this thread.

[booboo]

Yes, you are correct.
I have been trying different ways through different db's to get this to work. Obviously, I called the table two different names in my attempts. Sorry about the confusion.
I will continue to try to work this out.
Sorry to take up your time with my 'Tangents'.

[booboo]

Well I actually got it working, sort of. It does insert the data but returns an error that I can't figure out. I redid the whole db, and as you can see, names have changed. My code for AfterUpdate is:

Private Sub Form_AfterUpdate()
'Appends payment info to history'
 
INSERT INTO(TblPay.CHKNUM, TblPay.RECDATE, TblPay.PAY_AMT, TblPay.BAL_AMT)
WHERE (TblPay.BAL_AMT = ([BAL_AMT] - [PAY_AMT]))
 
End Sub

I changed the tables so that I am only doing an append and to only one table.
The error I get is:
"The expression AfterUpdate you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name."

I've tried adding the FrmPay name in the (), and various other things but....
Any help ?

[NeoPa]

I'm sorry.
I don't know what makes it so difficult for you to answer simple questions, but clearly this is not as easy for you as I think it should be.
Still my questions remain unanswered. Still you want to go off on tangents.
From my perspective, you've taken a wrong approach here and tried to reprogram the part that Access should be doing. Access should be handling the updating of the data within the form automatically.
How can I possibly explain this to you though? And how to go about doing it correctly? You ignore almost everything I say.

All I can say is good luck. I hope you find some help with your problems.

[booboo]

I'm sorry.
I don't know what makes it so difficult for you to answer simple questions, but clearly this is not as easy for you as I think it should be.
Still my questions remain unanswered. Still you want to go off on tangents.
From my perspective, you've taken a wrong approach here and tried to reprogram the part that Access should be doing. Access should be handling the updating of the data within the form automatically.
How can I possibly explain this to you though? And how to go about doing it correctly? You ignore almost everything I say.

All I can say is good luck. I hope you find some help with your problems.

I haven't ignored what you have said because virtually all you have said has been inferences of my lack of knowledge and the 'tangents' as you so like to use.
From my perspective, you have made no effort to even understand what I have said since everything is not explained exactly as you wish.
Yes, I hope someone can help but my bet is you have killed this thread from anyone attempting to do so.
If I knew all I need to know to build this, I wouldn't be on here asking for help.

[NeoPa]

I'm sorry you feel that way.
I would like to make clear though, that I have not in any way disparaged your lack of knowledge in this thread (feel free to reread it). If I had a problem with anybody's lack of knowledge I'd be pretty stupid to spend so much time here.

My problems have consistently been with your inability to respond to what I post. This seems to imply a lack of respect when dealing with others that you expect to help you.
If I can just quote from your own last post :

From my perspective, you have made no effort to even understand what I have said since everything is not explained exactly as you wish.

You seem to feel it is our job to make the effort to understand your problem, regardless of how little effort you put in to explaining it clearly or even bothering to reply to questions from people trying to help. Multiple posts referring to the same objects but by different names is just one example of your unhelpful style. I think you were given ample opportunity to correct this but it all seems too much effort for you. You prefer to blame others for your own problems.

I can ask others to have a look at your problem if you like, but I should warn you that most of our experts will probably want to steer well clear of you after reading what you've posted in this thread. I'll leave it up to you. Let me know if you'd like me to.