Form Wont Update, Cannot set an ID on record...

[d9pierce1]

TransBill.zip

Howdy and Happy Holidays for all.

I am very sorry to ask for this but I am stumped. I did get it to work but the method was just unacceptable so I made this mock up db (Attached)
So, the issue is I have a form, frmTransBillNew. Opens on startup....
Populates from Qry2,,,, and basically populates my datasheet for bills due once records are created.
Please keep in mind this is just a Mock Up of a form and some tables, Qrys... I had to pull out of my db to get small enough to put in here.

I have tried every thing i can try on this to get it to function correctly.\

First Issues is (This is a new one even for this) I select from combo the Business it comes from, that now gives me an error that it cant update, but yet after hitting OK twice it goes into record anyway...
Second, I want to set the AccountID by my combo, Bill To Account but it just inst having it. I have been working on this for a day and a half trying different methotods, means, and such... LeftJoins, RightJoins, new Qrys, Many new Qrys, and so on, Its like the AccountID is a total separate record and I jsut want to set it by a combo....

The point is in Query1 I have the info I need to assist Query2, but it wont let me update or set the AccountID field. I just cant figure out how to join these so that I can create a record ?

In any event, I would really appricate some help on this.


Thanks
Dave

[kd2017]

I'm not sure what you're trying to do here. It looks like after you select a 'Bill to Account' that you intend to autofill some of this information like payroll deduction, service fees, etc?? You can do that but I'm not sure that building an input form off of these queries is the best approach.

Are options like services, etc mentioned above supposed to be editable in this form? If any fields might change from one bill to the next then we might have a table design problem.

Why are tblAccount and tblAccountDetail broken out into separate tables with a 1-to-1 relationship?

[June7]

A form would normally do data entry/edit for a single table. Yours is allowing edit of fields from multiple tables. Why a complex query as RecordSource? Should simplify. Bind form to table.

AccountID combobox should include all fields as columns that are needed for displaying info about account, such as IsPayrollDeduct, ServiceFee, ServiceFeeType. Then expressions in textboxes can reference those columns by index (index begins with 0 so if value is in column 4, its index is 3).

=CboToAccount.Column(3)

Why have a textbox and a combobox bound to AccountID?

Definitely should not allow edit to lookup table fields.

[d9pierce1]

Hi June7,
I am really lost with this one. The Account and AccountDetail are part of other forms in my actual db. I need some of the info to from those tables to pass on to my Bill Form to enter a bill.
This is intended to eliminate having to fill these items in over and over as they are all ready in those tables and were entered in other forms. The only way I can link them is they both have
an AccountID which i need. However, when entering a bill i would like to select my AccountID via combo box. But it wont let me.... Select from the Business or person it comes from, then select the
combo for the account I want it to post on via combo. I dont know... Was supose to be a good way to do it in my mind but i do run short on film....
Thanks

[d9pierce1]

Hi kd2017,
What I am trying to do here is populate my bill table when i receive a bill. IN my query i am taking some things from each of the tables, tblAccount & tblAccountDetail. and adding that to my tblReceiveBill
Which populates a subform data sheet on my main form. What I am trying to do here is create the table data necessary to populate a data sheet.... so, I want to know who it was from, Business or Person,
and I want to select via combo the account it is realted to and some stuff that realtes to it. Blows me away I cant select an account for the account number. I did it a different way and it does work but it is
really ugly... Have to have a default AccountID such as 2, then have to select business, then have code behind that in afterupdate to save record, then it will allow me to select via combo the account i want,
and that changes the accountID... Really messy...but it does work somewhat....

This is just a sample Iput up there, i have done this 100 times at least different ways. I cant get around having info from the AccountID and AccountDetails, where I can select the accountID...
I dont know...
Thanks

[June7]

Check out this revision.

[d9pierce1]

June7
May God Bless You...
This is exactly what i was tryig to accomplish....
You have no idea how greatful i am for this, I cannot beleive it.
Thank you so much...
Perfect

Dave

[d9pierce1]

Hi June7
Is there a way to get the Service Fee Applies and Payroll Deduction check boxes to check if true?
Not sure if possible to accomplish this with taking the value from the combo column....
I played around with it a lot since you sent and that is the only thing that would make it better....\
I am sure it has a value but not sure what that would be. I know it knows to apply anyway.

Thanks
Dave

[June7]

Sorry, expression for ServiceFeeApplies checkbox is referencing wrong column. Change 5 to 3.

Otherwise, the checkboxes are reflecting value from combobox columns.

[d9pierce1]

Thanks June7
I didnt even think of that as a possibility...
Again, Thank you,
Dave

[June7]

Because I am soooo infallible!! Thank you!

Glad it's working.