My form is changing my data on load and close

[Hammotime]

Hi All,

Struggling with this form. Need help please.

First of all, just a general thanks for anyone that responds on these forums. It has really helped me out in the past.

I have designed a form using the wizard from a table which records transactions for an entity.

I have a Primary Key (auto number), entity id, transaction amount, description and date fields in the table.

I look up the entity using a list box, then populate unbound fields then use CurrentDb.Execute "INSERT INTO in order to save the new record.

When I open the form for use, it automatically changes the entity id of a previously added record to the most recently selected entry in the list box. I am unsure why it does this. It seems to be editing previous records when you change the value of the list box.

Any thoughts? You are welcome to ask more questions if my explanation sucked.

Thanks in advance.

Rob

 

[Mile-O]

A few things:

1. If you have the EntityID in your main table, the Listbox should be bound to this field, and you shouldn't have to add other elements from your Entity table. The reason? You would join the tables in a query to pull together the information you need.

2. If your structure isn't a table each for entities and transactions then your database design is in need of restructuring.

3. You did have a WHERE clause in that SQL, didn't you? This is likely to be what your problem is, although I would say it's actually one of structure and trying to store unnecessary data (you have the entity info in the entity table, why store it a second time in the transactions, when you can just create a link [the ID] between the two?)

 

[Mile-O]

Or, in other words, you structure should be:

tblTransactions
TransactionID (PK, autonumber)
EntityID (FK, number)
Description
Amount
etc

tblEntities
EntityID (PK, autonumber)
EntityName
etc

So, rather than store the entity information in the transactions table, you would just use a query when you need the further entity information. By which I mean you join the two tables together on the EntityID field, and include needed fields from each table.

 

[Hammotime]

Hi Mile-O,

Thanks for the prompt response. I think you are on the right track with the Where clause but just to clarify my table structure is as you suggested. I think I didn't explain myself well originally.

The main table is the entity table and the transaction table I linked to the main entity table via the entityID which is my FK in Transaction table.

The Listbox is bound to entity table using the following record source:

SELECT Payments.*, Household.ID AS ID_Household FROM Household INNER JOIN Payments ON Household.ID = Payments.[Household ID];

(Payments is transaction table and Household is Entity table)

But to clarify what you meant about the WHERE clause in the INSERT sql, the issue I am having is occurring without me even using the button which uses this code, which is where I am a little confused.

Thanks

 

[Mile-O]

The Listbox is bound to entity table using the following record source:

I assume you mean the form the Listbox is on is bound to the Household table (better still, a query based on that table) and the listbox is bound to a field within it. Then the Listbox source should be a query based on the Payments table.

Should your Listbox SQL not just be:

SELECT * FROM Payments WHERE HouseholdID = [Forms]![YourForm'sName]![AControlWithYourHouseholdID]

Substituting my generic terms of course.

As I said, I think the INSERT INTO query is an added complication that you have introduced. If you have the foreign key in one table as the primary key in anther, then a query joining the two should be all that's needed to get the extra fields.

Or, are there multiple transactions per household (seems likely) that you want on your form?

Could it be that your form is for the data entry of households

What element on your database is this form with the Listbox used for data entry? Households or Payments?

 

[Hammotime]

You are spot on about the List box. It is query of ID, Last name and First name from the Household table.

The user will select the household from the list box, fill in the other fields then click the Process button activating the INSERT sql which will add a record to the PAYMENTS table. It is only used to add transactions to the Payments table. These transactions must not be edited.

So yes there will be several transactions per household.

The current problem is that when user clicks on a household from the listbox, it is going to a default record and overwriting the FK with the new selected household.

By using your statement for the Listbox, will this stop the user from affecting previous enrtries into the Payments table?

Thanks

 

[Mile-O]

Have a look at my rough two-form example attached. Not an INSERT INTO in sight, yet it manages payments.

Does this help?

 

[Hammotime]

Hi Mile-o.

its very nice of u to create a mock up for me. really appreciate time u spent helping me.

Having said that you will probably think I am pretty stupid for not being able to make mine work the same way.

first I went through your form settings and changed everything in mine to match urs. this includes using a query for the listbox,a query for the record source on form, using acaddnew instead of insert and a few other adjustments. still same problem. so I thought it migjt be.something with my initial design.

I actually remade the form from scratch.

1st attempt I used a blank form and added all content manually matching ur form. this form would not save new.records to the table.

2nd attempt I used a form attached to the table (not the wizard). I was able to add new records to the table and this one is my closest attempt to success but the prpblem.with this is similar to original issue. this one preloads the first record.from the table into the form and anything I change edits that record.

I am trying to have form created so that transactions cannot be edited from the form once entered.

I am starting to pull my hair out as something you made look so easy is failing on my db.

i know i have practically killed.your time but any suggestions.?

(ps I think I owe u beer if ever in Melbourne.)

 

[Hammotime]

Hi Mile-O,

Just to let you know I think I may have solved the problem on the original form with a workaround.

The close button I was using was created using a macro.

I used your code from the Cancel button and seems to not be remembering the previous transaction. Not sure if I have actually fixed the issue or just found a way around it but at least it works after all the testing I have done.

Wanna thanks you for your help. Serious about the Beer if you are ever in Melbourne.

All the best.