X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Open Form Based On Specific Record, Access 2007    
post Oct 18 2017, 04:51 AM

Posts: 3
Joined: 18-October 17

I am really having huge problems with this and after many many issues I think I might have understood what's happening but have NO idea why or how to fix it.

I have 3 main tables - customers, the worksites for each customer, and the jobs that get done at each worksite. Fairly straightforward.
Each table is linked by an ID Customer ID, Worksite reference and Job number.

My main form is a work order entry form based on the worksites - you select a worksite from a dropdown list which then pings up all the relevant info on that worksite in the main form plus two subforms - 1. a list of all the servicing dates for that worksite and 2. the jobs associated with that worksite.
The second subform is where I am having issues.

My form very happily pings the worksite down into the jobs subform and I can scroll through all the different jobs done at that site with all the correct information, add new ones, edit data etc. All lovely. I have other buttons to open up the detailed worksite information relating to specific customers and detailed information for specific customers.
Where I'm running into problems is with adding buttons to open a new form related to my jobs form. Each job will be of a different type - a fire extinguisher service, an emergency light service, and intruder alarm service and so on and each of these has its own form to be printed and filled out on site.
I need each button to open up that specific form for that specific job - I don't want to have to scroll through thousands of jobs every time but the macro isn't working. I select Jobnumber as the common control on both forms and yet every single time the form opens to the first worksite in my database, and not the worksite/job I am currently looking at.
I have tried various things:

In the macro for the where conditon I have tried

[JobNumber] = Forms![ELS]![JobNumber]


"[JobNumber]=" & [Forms]![ELS]![JobNumber]

and I have even tried the VBA with DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName
but nothing seems to work.

What I HAVE notices is that when looking at the button wizard, the items populating the left hand pane appear NOT to be the correct controls - for example in my jobs table I have a field called DateReceived to show when the job was raised BUT in the button wizard, it is showing up DataEntryDate which is a control from the old imported data that I have since deleted.
I'm assuming this is the problem with my buttons - the database is seeing the wrong object but that object just doesn't actually exist and that control isn't called that anywhere that I can find!

Go to the top of the page
post Oct 18 2017, 05:57 AM

Posts: 3
Joined: 18-October 17

I have got some of the offending buttons on my form with various different methods of opening said forms:

Jobsheet I have tried the wizard with JobNumber as the shared control
Jobsheet 2 I have tried with the where condition as [JobNumber] = Forms![JobsheetAndFEX]![JobNumber]
Jobsheet 3 I have tried with "[JobNumber]=" & [Forms]![JobsheetAndFEX]![JobNumber]
ELS I have tried with the DataEntryDate & Date Received as the shared control
Fire Alarms I have tried with Worksite as the shared control
Smoke Alarms with the VBA code.

None of these combinations seem to work... *sigh*
Go to the top of the page
post Oct 18 2017, 06:12 AM

UtterAccess VIP / UA Clown
Posts: 32,213
Joined: 21-January 04
From: LI, NY

In a macro, you need to use

="Jobnumber = " & [Forms]![ELS]![JobNumber]

as your WHERE clause. Assuming Job Number is a numeric datatype.

In VBA you would use:

DoCmd.Openform "formname",,,"Jobnumber = " & Me.[JobNumber]
Go to the top of the page
post Oct 18 2017, 06:30 AM

Posts: 3
Joined: 18-October 17

Well, my database appears to have fixed itself... somewhere along the line (and I have no idea when or how) my query that I had invoked to make my jobs go in job order had disappeared and the form was based purely on the table... having put that query back it seems to now be as happy as a dog with a bone.

The VBA was definitely the way to go!

Thanks for the reply though! grin.gif thanks.gif
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 08:30 PM