search multiple fields

[Greg]

I’m more of a beginner in Access I have to admit, but I think the Main Form EntryHead makes sense because the user enters data in this form only 1x (unique data) and everything else then continues and finish in the Subform. That seems fine to me.


It is not clear what you mean by "between several fields".


For example, when a combo box appears in the Customer field, I can now only select the customer ID as the number (e.g. "500"). However, I would like to be able to somehow move to the next field and also be able to select by customer name as well (e.g. "Company A").
If this is not possible I will add two combos to the form one for customer ID and one for a customer name and somehow solve this. However, it would be nicer and faster if the user could choose all this in just one combo.

[June7]

Okay, think I understand EntryHead now. All EntryHead fields (except the autonumber) are duplicated in GeneralLedger. You are using a 5-field compound link on subform. So there will be multiple entries in GeneralLedger for each EntryHead record?

Relationship Builder links are wrong. EntryHead should link directly to GeneralLedger, not through DocumentType.

There is no designated primary key in EntryHead. If you want EntryHead then set its ID as primary key and remove duplicate fields from GeneralLedger. Set the 5 fields in EntryHead as a compound index to prevent duplicates. Save EntryHead ID as foreign key in GeneralLedger. Link the subform on primary and foreign key fields.

Shouldn't CustomerID be in EntryHead table, not GeneralLedger?

Also, Relationship link between GeneralLedger and DcoumentType is wrong - it is not linking with a primary key. Since this GeneralLedger field will be removed, keep link to EntryHead. But either save ID as foreign key in EntryHead or designate DocType in DocumentType as primary key.

Could have a combobox set up to allow input of number or name. It would involve a UNION query as RowSource.

SELECT ID, [Name] AS Data FROM Customers
UNION SELECT ID, ID FROM Customers
ORDER BY Data;

Or have two comboboxes bound to same field and both save the ID.

If I didn't already say - advise not to use spaces nor punctuation/special characters in naming convention.

Also advise not to build lookup fields in table.

[Greg]

I solved the problem of how to search between two fields (ID and Name) by adding two fields "CustomerID" and a field "CustomerName". Then I put the code Me.CustomerID = CustomerName and to some extent works well.

You mentioned to me, Relationship link between GeneralLedger and DcoumentType is wrong, to some extent I agree and would like to change something now.

What I would like to make is data from the main form to be automatically written to the subform BUT with the possibility that they can be changed manually in the subform. Now this is not possible because the relation is linked (as mirror) and every change in the subform also changes in the main form, which is not okay. I would like some data to be just copied to the subform (as a copy not mirror). Do you have any idea how to do this?

Thanks again for all the responses.

[ArviLaanemets]

Having lookup fields in tables makes designing linked forms very difficult - and you have a lot of them!

[mike60smart]

Hi Greg

Relationships should be setup as shown in the attached.

You should not have Lookup Fields in tables.

Each Table should have an Autonumber Field whic should be the Name of the table ie tblCustomers would have an Autonumber field of CustomerID.

The Main Form Primary Key named EntryHeadID is linked to the tblGeneralLedger on the Foreign Key named EntryHeadID

Study the relationships and the names of Tables

[Greg]

Hi Mike,
I have already changed the database in the meantime, I am currently in this database which I attached (v6.4). I think the relations in this database are correct.
Now I would like to rearrange the fields, but I got stuck because I don't know how to read (copy) the data from the main form to the subform. Linked fields are not the appropriate solution in this case!

I would really appreciate any more idea how to solve this problem from your side.
Thank you.

[mike60smart]

Hi Greg

Sorry but I have no idea what you are trying to achieve with this database.

I gave you an example of how to relate tables and your latest version does not contain any relationships.

All you have done is joined certain fields to other fields. This will not work

[mike60smart]

Hi Greg

If you PM me we can discuss your problem

[kd2017]

Moving the conversation to private messages or email kind of defeats the purpose of a public forum.

[kd2017]

Hi Mike,
I have already changed the database in the meantime, I am currently in this database which I attached (v6.4). I think the relations in this database are correct.

I disagree. As has already been mentioned you're duplicating data in two different tables. This is truly not necessary.

You've also linked the customers table to these tables twice, it appears so that you can show the name and the code? This is also not necessary. You can use a query or other means to pull information into your form for presentation, but you generally only want to link to a related table once.

Now I would like to rearrange the fields, but I got stuck because I don't know how to read (copy) the data from the main form to the subform.

If you use a single table, instead of two, then there will be no need to copy data. Based on what it looks like you're doing with the EntryHead/GeneralLedger form/subform setup you can accomplish this with one form, GeneralLedger, using a split form for data entry.

Linked fields are not the appropriate solution in this case!

I would really appreciate any more idea how to solve this problem from your side.
Thank you.

I've made some modifications to your database for review, check out "NEW_FORM", play with it. I'd say you should work out a solid table structure design and then tackle the issue of searching multiple fields. Even with the changes I've made I know it's not nearly optimal, but to help with that we need to know in plain english what this database is supposed to accomplish.

The field searching wont be a problem btw.

It looks like you're making some accounting software here. Are you sure you want to reinvent the wheel? If you do here is a huge list of example data models, keyword search for "accounts" or invoicing or something might turn up something applicable for you: http://www.databaseanswers.org/data_models/

[mike60smart]

Hi kd2017

Realise it is best to keep a topic in forum but sometimes the only way to resolve a particular problem is to actually speak to the person that has the problem.

Just my opinion.

[isladogs]

Hi kd2017

Realise it is best to keep a topic in forum but sometimes the only way to resolve a particular problem is to actually speak to the person that has the problem.

Just my opinion.

I agree with kd2017 and made the same comment to you at AWF.
An exchange via PM is not 'actually speaking to the person that has the problem'.
It just means that the ideas exchanged cannot be seen by others who may also have useful contributions. Nor will any PM comments be available to future readers of this thread

[Greg]

Hi guys,
I haven’t made any private message so far because I also don't know how to do it. I don't have an email address from mike60smart. However, in the meantime I've already found a way to copy data from the main form to the subform.

Thanks for your responses and suggestions.

Regarding duplicating data, I really don't know where is a problem, I will have to solve this at least when the problem shows up…
Just for a hobby, I am trying to make a General Ledger step by step at first. I found some ideas on YouTube though I don’t know anything arabic or hindi (see link below) but this was the only good tutorial for accounting that I could find so far.

I will be very happy if you coul'd give me any idea and help in my future questions.

https://youtu.be/AOTiTCTCx-g

https://www.youtube.com/channel/UCLy...YgacbGw/videos

Thanks

[mike60smart]

Hi Greg

Can you explain why you do not want to use the normal linked MainForm / Subform setup?

Also, Why do you need to copy data from the Main Form to the Subform??

[Greg]

Hi Mike,

maybe is my concept total wrong. But what i just did now is that I added yet to the subform/table GeneralLedger an ID field from table/form EntryHead as a FK, made a relationship and made a master-child link between them. I kept link for 3 other field as well (Document, Date0 and Doctype). All other data are now automatically copied to subform via Button (Save/Post). I locked almost all fields in subform so that only in main form could be the data entered.
What I need to do now is find out how to move key cursor direct to a specific field in main form AND at the same time clear some other fields in a main form AND ALSO in subform move cursor to the next record, all with that one action. I hope that such a concept is possible and logical??