VBA code help

[TomJ58]

Hi All,

Our company uses an html application in conjunction with SQL called CoStar to house protocols that the Computer Operations department uses to run all of our daily and adhoc production jobs. Because this application does a poor job of reporting, current leads have resorted to various Excel spreadsheets to track these protocols by job number, shift used, type of job, etc.

As a new lead and the one with the most knowlegde of MS Access, I took on the task of combining the spreadsheets into a single database. I have created the database and used the datasheet form view to simulate the speadsheet veiw that the other leads are used to seeing. However, the master table contains more fields than are displayed in the datesheet view. I have created tabular subforms to add and edit records.

After demonstration the database to the other leads, an enhancement was requested. If a record is selected in one of the datasheet subform views, that action (OnCurrent?) will open the tabular subform to see/edit all the fields in that record. I considered expanding the datasheet view in the form to include all fields, but that would be impractical due to the length of the data in a number of the text fields.

I am a novice when it comes to writing VBA code, so any help would be appreciated.

 

[pbaldy]

Is this appropriate to the situation?

http://www.baldyweb.com/wherecondition.htm

 

[TomJ58]

Thanks for the response. However, I don't understand what the ControlName is in the Me.ControlName.

 

[pbaldy]

It's the name of the control (text box, combo, etc) containing the value to filter on.

 

[TomJ58]

This is the code I am using:

Private Sub Form_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmVieworEditaCostarJob", , , "JobNumber = " & Me.WorkingJobNo
End Sub

However, I get a Compile error "Method or data member not found." JobNumber is the name of the master table field that I am retrieving the record from and WorkingJobNo is in the form that I am selecting.

 

[pbaldy]

JobNumber is a field in the form being opened as well? Can you post the db here?

 

[TomJ58]

JobNumber is the field in the master table and is the Contol Source for form I am trying to open. WorkingJobNo is the field on the WorkingJobs form that I am selecting the record from.

I am not sure how to post the db. I have it zipped but don't know how to upload it.

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[TomJ58]

Here is the db. The code in question is using the OnDblClick property of the frmWorkingJobs form.

 

[TomJ58]

I found the problem. I made changed to the field names in the tables that the forms were using. It appears that some of the name changes did not propogate to the Me.ControlName. Thanks to pbaldy for his assistance.

 

[pbaldy]

Happy to help.