Using selection to populate data in form

The common task is the method I have been describing and not the duplicate data method you have described. Your method is always advised against as it introduces duplicate data and more possibility for errors.

Yes - it is easily done without duplicating data. The records in Table 1 are “Linked” to the records in Table 2 via the Table 1 primary key. This is the common method used.

So two separate entries might look like this…

  1. SF2506 (job number), Meanwood Castle (site name), John Smith (client name), ballast mix (sale item) and £300 (price)
  2. SF2506 (job number), Meanwood Castle (site name), John Smith (client name), soil (sale item) and £500 (price)

Sorry but I may be missing a basic understanding here. To compliment the SQL method in your answer above, do you know of any other tutorials that might help me understand how to do this please?

Give me a bit of time to put together another sample based more upon your description. Will post as an edit in my answer.

thanks so much, I am getting quite frustrated with this because I know it must be simple and so I am missing something basic. Thanks Oliver.

Thanks so much for this, I will check it out shortly.

That looks really good so far, the use of the filter table seems to solve it.
I’m yet to apply to my database but thank you very much.

@oli1 This latest sample is actually the same sample (I just copied) pointed to originally (ShippingInvoice.odb). The only difference is table names and one less listbox.

If this has now answered your question please click on the :heavy_check_mark: (upper left area of answer).

Ok so I have the jobs number selection dropdown and client and site name text boxes set up as in yours, but the text boxes only update with the job number in the jobfilter table when I close and reopen the form??

Its ok I sorted it I must have inadvertently scrolled the mouse wheel after selecting ‘refresh form’ action for the push button to ‘no action’

Ok its all working great now thanks.
But Im using form fields instead of a table for the subsubform to enter the data into the fees list, because I prefer it for the user experience. But if the user presses return at any point when filling in the form all the boxes go to blank and if you try and re-enter the information (starting with the job number at the top) it says something about cant insert a null into a non-null and displays nothing in site name and client name. Any ideas?

You probably have a default push button on the form which is executing when Enter is selected. This likely causes a new selection from the list box which then tries to bring up new information. Before the new information can display, the entered data must be updated. If it is not completed according to your settings you get stated results.

Thanks, where in the form settings should I be looking to change this?

Don’t know what your question means. Change what? Default of button? That’s a button property.

So if for instance I enter a fee amount of £250 then press enter instead of selecting the next box via ‘tab’ or the mouse, it causes the problem. So is there a way of stopping this from happening? All push buttons in the form are set to either ‘refresh’ or ‘save’ which is what I want them to do. I just want to ‘block’ whats happening if ‘enter/return’ is pressed.

So if for instance I enter a fee amount of £250 then press enter instead of selecting the next box via ‘tab’ or the mouse, it causes the problem. So is there a way of stopping this from happening? All push buttons in the form are set to either ‘refresh’ or ‘save’ which is what I want them to do. I just want to ‘block’ whats happening if ‘enter/return’ is pressed.

I guess you don’t understand that you have me at a disadvantage. You are asking me to debug something I know nothing about. You designed the form (it is obviously not what I posted) and I don’t know what is on the form or how you have set controls or what defaults are set. Did you check the push button properties? There is a Default button... setting which if set may be your problem.

ok sorry I appreciate that, but cant seem to add a copy of my DB in comments for you to look at. Failing that where would the Default button setting be?

As stated in each of my last two comments, it is a property of the button. Right click button, select Control and look under General tab for Default button....

Keep in mind, the original question has been answered. This forum is designed to ask a specific question & get an answer. These recent questions really should be a new question to allow others searching for answers to find.

ok, and thanks for all your help :slight_smile: