Replies

Well, I finally am not getting that “Microsoft Jet database engine…” message anymore. It ended up being the join properties that were the problem.

But now, I can’t pull in the Total Applied into the Applied Subform from the ApplyAmount form. I put the following in the Control Source line of the ASTotalApplied field of the Applied Subform:

=[Total Applied]

That is the name of the field in the ApplyAmount form. But it just gives me an error. I also tried using:

=Forms![ApplyAmount]![Total Applied]

but it didn’t like that either. What am I missing? Boy this section is a real nightmare!

Oh Boy! I’m just going around in circles! I added in all the fields for both the InvoiceDetails table and the Applied table when I did my ApplyAmount Query. And when I do that I just keep getting a message saying “The Microsoft Jet database engine cannot find a record in the table ‘tblInvoiceDetails’ with key matching field(s) ‘tblApplied.LineNo’.

Am I getting all these problems because I’m using the wizard to do the Apply button? When I use the wizard, it asks which fields have matching data for lookup. I noticed that the other LineNo field on the Applied table which I did not match is the one that the Microsoft Jet… message refers to. Help!

Am I doing the wrong kind of query? I’m just doing a regular query. It seems to do okay with the InvoiceDetails table in the query, but when I add the Applied table, it just gives me trouble. Is it because I have no data in the Applied table?

Okay, I dragged all the fields onto the query grid. I tried to change each field, and I got three fields that could not be changed. It was tblInvoices.InvID, LineNo (from tblInvoiceDetails), and tblFees.FeeID.

When I go into the Applied Subform and try to add a line, it won’t let you. It won’t let you enter the tblInvoiceDetails.InvID OR the Amount (from tblInvoiceDetails). When you try to enter something in the Amount field, it says “join key of table tblInvoiceDetails not in recordset.” What does this mean?

EEGADS!!! I just can’t seem to get this to work!
————————-
…a related record is required in tblInvoiceDetails. What does this mean? Am I missing something?
————————-
I don’t think I’m understanding you correctly, ’cause I just can’t get it working. I still get the same message. In my Payments form I have the Applied Subform that shows the following:

StudID LineNo InvID FeeName Amount Total Applied Outstanding

Now, from StudID to Amount, I get from a query. Is that right? I’ve pulled from the Invoices table–StudID, InvID. From the InvoiceDetails table–LineNo, InvID, and Amount. From the Fees table, just the FeeName. I don’t know if this is where I’m going wrong, or what.???

Then for the ApplyAmount form that you go to in the Applied Subform (there’s an Apply button), I used the Applied table (I pulled it in). That consists of fields: PayNo, LineNo, InvID, and Applied amount. I then used syntax in the controls for the LineNo and InvID. I also made a text box in the footer of that form to calculate the sum of the applied.

I get the “related record is required in tblInvoiceDetails” error when I am in the ApplyAmount form. I’m just going around in circles here! I hope you can help.

Alright, I got it to work! Thanks so much, Thomas!

Okay, now I also want to take what I have on the Deposit Report and make another report that lists just the cheques–to make a cheque listing to put with the bank deposit. Do I have to make another query that only lists the ones that have Cheque checked off? I would have a screen with two buttons–one printing the deposit report and the other printing the cheque listing. And then when I close that screen it would run the update query putting in the deposited date. Would this be right?

Edited by JenniferS on 24-Aug-01 17:49.

But how do I get the report to print only the ones that have no date in the Deposited field? I want to go into a screen that you press a button to preview then print the Deposit Report then it will enter that day’s date in the Deposited field. But when I go in there again, it should only pull up those that have no date in the Deposited field. How do I do that? I’m quite new to access and have been teaching myself and learning from you guys.

When you go to put an auction up you have to select which category you want your auction to go in and then you have to enter your UserID and password. You then get the form that asks you for the auction information: Title; Description (in the description you can enter some html to add in some extra pictures); your Main Picture; Location (city, state, etc.); Length of your auction (3, 5, 7 or 10 days); and then there are some other boxes that you check off for the kind of payment you want to receive (e.g. money order, personal cheque, etc.). Also, in between these are some boxes that ask for info that I don’t need to put in so how would you leave these out? At the end of the form there is a Submit button that goes to a preview screen before you confirm posting of your auction.

I want to be able to design a program that can bulk upload several auctions at a time. Then I want to be able to download any additional information when the auction ends (like the name of the winner, winning bid amount). I then want to set it up with a section under each auction where I go to e-mail the bidder, put in the shipping cost of the item, and how they paid.

I don’t know if you can do this with Access. I know there are a few programs that you can buy that can do this, but I just wanted to see if I could make one up myself. I just don’t know how you would find out where to transfer the data into Ebay and how to get it out.

Sorry about that. Here’s the answers to your questions:
———————————-
On the screen I see; LineNo InvID Amount Applied Outstanding PayNo
———————————-
This is on the ApplyAmount form.

———————————-
…a related record is required in tblInvoiceDetails. What does this mean? Am I missing something?
———————————-
Okay, in the SQL–Query Builder I have the following:

The tables that are showing are the tblInvoiceDetails, and the tblApplied. Under the tblInvoiceDetails I have the fields–LineNo, InvID, FeeID and Amount. There are two key fields–LineNo and InvID. Under the tblApplied I have the fields–PayNo, LineNo, InvID, PaymentID and Applied. LineNo and InvID have a one-to-many relationship with ONE being the InvoiceDetails table.

In the query area I have the LineNo field from tblApplied, InvID field from tblApplied, Amount field from tblInvoiceDetails, PayNo field from tblApplied, PaymentID field from tblApplied, and Applied field from tblApplied.

On the ApplyAmount form itself I went into properties for the boxes: LineNo; InvID; and PaymentID, and I used the formula you gave me (Forms!FormName!SubformName!Control) to pull in the data from the Applied Subform and the Payments form. I hope this is the information you need.

————————————
how do I get the oustanding amount to now show in the Amount column after each payment?
————————————
Okay, on the Payments form I have the payment info (StudID, PaymentID, Date, Payment amount, ChequeNo, and check boxes for whether cash or cheque). Then below that is the Applied Subform which consists of: LineNo, InvID, FeeName, and Amount. Next to each line I have a “Apply” button that goes to the ApplyAmount form.

On the ApplyAmount form I have: LineNo (from the Applied Subform); InvID (from the Applied Subform); Amount (from the Applied Subform); Applied (from the Applied table); Outstanding (just a text box with a formula to deduct Applied from Amount); PayNo (an autonumber field from the Applied table); and PaymentID (from the Payments form).

When I go to apply a payment in the ApplyAmount form, I enter the amount to apply under Applied and then the Outstanding amount shows up (e.g. the Amount is $35, I enter $5 to apply and the Outstanding says $30). Now what I want to happen is when I close out of the ApplyAmount screen, that line I just applied to should show the Outstanding amount on the Applied Subform. Do you know what I mean? It should amend the Amount column on the Applied Subform. OR maybe I shouldn’t be doing it like that. I don’t want to change the amount that is in the InvoiceDetails table.

I hope this is enough information. If you need any more, just let me know.

No I don’t. Should I put that in? How do I do that? I get very confused using Access. How would I get the program to mark the payments as deposited?

Edited by JenniferS on 23-Aug-01 20:08.

Okay, I got the LineNo and the InvID to show up on my ApplyAmount form. But now I still can’t get the totals to work properly. Here’s what I’ve setup:

On the screen you see; LineNo InvID Amount Applied Outstanding PayNo

The Outstanding amount has a control– =[Amount]-[Applied].

When I go to close the window it says that I can’t save the record because a related record is required in tblInvoiceDetails. What does this mean? Am I missing something?

And…how do I get the oustanding amount to now show in the Amount column after each payment? And I would then have to transfer it to the Applied Subform so the Amount there shows the same as the AppliedAmount form.

______________________________

I now added the InvoiceDetails table in the query builder, and added in the columns LineNo from Applied Query, InvID from Applied Query, and Amount from InvoiceDetails. Did I do this wrong…because now it says that the Microsoft Jet database engine cannot find a record in the table “tblInvoiceDetails” with key matching field(s) ‘LineNo, InvID’. But those fields are in the InvoiceDetails table.

How would I read the values directly from the subform? Do I use the same query that I used for the subform?

I’m a little confused. I’ve just been teaching myself Access as I go along. This is the toughest program I’ve ever had to learn. Did you find it being very hard to learn? I just don’t understand what all the controls mean and do, and how the program reacts to certain changes. If there was a book that explained what each property meant and what it did that would sure help. Do you know of any such book?

Edited by JenniferS on 21-Aug-01 22:38.

That sure did help, but…

I have made up the Payment Form with an Applied Subform. I’m not sure how to take the Amount of the fee less the Applied and show it as the Amount the next time another payment is entered. Here’s an example:

(This is for a $10 Payment)
FEE AMOUNT APPLIED OUTSTANDING
Elementary $35 $5 $30
Local Levy 5 5 0

Now when I go to enter another payment, like for $15…

FEE AMOUNT APPLIED OUTSTANDING
Elementary $30 $15 $15
Local Levy 0 0 0

Is there any way to do this?

—————————————————————-

Okay, now I have changed it around a little, but still having problems. I have the Payment Form setup so that it has a subform that displays StudID, LineNo, InvID, Fee Name, Amount, and then a button that says “Apply” that opens a frmApply Amount form. But when it does this it won’t pull in the LineNo and InvID. Why won’t it do this??? I wanted it to show the line number (because the “Apply” button is after each line of the invoice) so you could then enter the amount you wanted to apply.

Okay…How about another printing problem? I go to enter a payment on the Payment form, and when I press the “Print Receipt” button it comes out with a blank receipt. You have to click on the next record arrow to go to the next record, then go back to the previous record (the payment you just entered), then when you press “Print Receipt” it shows up. How do I get it to print out the receipt without having to do all that?

Also, when I press on “Enter Payment” from the main Student screen, it goes to the first record. How do I make it so that when I click on “Enter Payment” it goes to a blank record?

Appreciate any help.

I fixed it! But now another screen that I have is probably having the same problem. I have a summary screen on the main Student screen that shows the invoice total and the total payments, but it is not showing up. I just entered an invoice. How do I get it to show up without a payment? I used a query that pulled in the Student table, a Total Invoice Query, and a Total Payments Query. I tried adjusting the Join properties but that didn’t seem to work. Do you have any idea what I might be doing wrong?

———

Okay, I fixed some of it. I now get the lines to show up on the Account Info Query whether there is a payment or not. But I still can’t get it to show up on the Student form. I noticed when I open the Account Info Query, it won’t put in the StudID. It will only put in the first one, and then it leaves it blank for every record after that. Do you know why this is? (I’ll keep playing with it–if I figure it out before you do, I’ll let you know). Thanks.

———-

I can’t believe this! I ACTUALLY FIGURED IT OUT ON MY OWN!!! YIPEEEEEEE!!! Well, thanks for listening to my complaints and questions anyway, Mark.