Stuck in a rut (1 Viewer)

[Confuseduk]

I'm new to this forum and quite new to Access.

I know that I can’t be the first to ask this question, but I’m struggling to find the answer. :banghead:
I have a Customer Table and a Job Table, plus their two respective forms. They are related by a one to many relationship between Customer ID and the child Customer ID in the job table.
I also have a working customer search that shows its results in a Results form.
Can I put a button in this Results form that takes me to a new Job Form with the job details empty but with the customer details automatically filled in that will populate both tables after update?
I hope this make sense to someone out there.

 

[June7]

Probably should consider a form/subform arrangement.

 

[Confuseduk]

Thanks for this. I have seen this option but for simplicity I would like to keep one form to each page as the job form is quite complex.
As things stand I can call up a new empty job form from my switchboard, but I have to fill in all the customer details again by hand to get it all to work.
I am however self taught and not as young as I once was.

 

[mike60smart]

Hi

Can you upload a zipped copy of your Database?

 

[Confuseduk]

I will do.


Thank you so much.

 

[jdraw]

In some set ups, you may have 3 tables.

Customer-->CustomerJob <---Job

along this same line

Student-->StudentTakesCourse<--Course

where
Student-----represents all details of a Student (could have X Students)
Course------represents all Details of a Course (could have Y Courses)
StudentTakesCourse--- represents Courses Taken by a Student( no one Student takes all courses...)

 

[Confuseduk]

Hi

Can you upload a zipped copy of your Database?

Here is a copy for you but i fear it doesnt work too well as i tried to enter data for you and the job table sent back note saying it could not be saved.


Now I feel very stupid.

View attachment Copy to Send1.zip

 

[mike60smart]

Hi

Which Form should I open??

 

[mike60smart]

Hi

Can you explain in detail what you are trying to achieve with this database?

You have a Customer table with a number of fields which should be in another table as they do not describe the Customer:-

Fields are:-
Paid in Full
JobNos
Job Number List


You should not use spaces in field names.

You then have a table which appears to combine Bookings and Jobs ??

This table "Booking and Job Table" needs to be normalised.

 

[gemma-the-husky]

if you know the active customer, you can use a hidden field, and populate that in the form's before update event.

me.OrderCustomerID = activeCustomerID

That's all that happens when you do it as a form/sub-form. This gets filled automatically.

 

[Confuseduk]

Hi

Can you explain in detail what you are trying to achieve with this database?

You have a Customer table with a number of fields which should be in another table as they do not describe the Customer:-

Fields are:-
Paid in Full
JobNos
Job Number List


You should not use spaces in field names.

You then have a table which appears to combine Bookings and Jobs ??

This table "Booking and Job Table" needs to be normalised.

Thank You Mike
One of the problems that I have is posing the correct question.
I have data in my version which I can't share on an open forum, but as a simple database it seems to work well.
If I run a search query from the Switchboard it finds and opens the customer form and I can open existing jobs for that customer in the Job Form. (quite pround of myself actualy for that)
What I would really lke is to have a button on the customer form that would open the job form with the customer name and address and telephone numbers already inserted.
At the moment it opens jobs pertaing to that customer but if I enter a request for a new job it clears all the text boxes.
I realise that I have probably bitten off more than I can chew.
And the database is very embrionic, so some fields i wont use including the three you mention, sorry about the spaces, and the name of the form is just a title. I havent thought about bookings yet, also I dont understand 'Normalise' so I'll go away and do my homework.
Maybe I should start again.
Anyway thank you for your help, I probably need to go and find someone who I can show it to with the data in, it may make slightly more sense.
I realise now if I cant pose the question, I'll never understand the answer.
By the way, thank you Alan for your reply, I'll read up on that.


I'm just an old cab driver so I'll stick to pen and paper for the moment!
Nigel

 

[gemma-the-husky]

What I would really lke is to have a button on the customer form that would open the job form with the customer name and address and telephone numbers already inserted.
At the moment it opens jobs pertaing to that customer but if I enter a request for a new job it clears all the text boxes.

when you do this open the form in data entry mode, with the customer passed in as openargs.

data entry mode does not show existing data, just new data.

Then in the current event put this

if me.newrecord then
   initiallize the fields, based on the customer you want to see
end if

 

[mike60smart]

Hi Nigel

I have modified the way you view a Customer and their Bookings/Jobs

If you do a Search for a Customer named "John Smith"

Then when the Customer Form is displayed select Go To Customer Details

The customer will be displayed as the Main Form and the Booking/Job Details are displayed in a Subform.

If you need to add another Booking for the Customer selected then use the navigation buttons in the Subform Header.

Hope this helps.

View attachment Copy to Send1.zip

 

[Confuseduk]

Mike you are an absulute gem.
Thank you so so much for your invaluable help.
I'm going to try to keep learning this until I'm not so stupid, I needed help over this hurdle, been stuck here for months!

 

[mike60smart]

Hi

We were glad to help