Use the Command button wizard to create the desired code.

 

Hey everyone!!!
I would like to double click on a field in data sheet view of a sub-form with combos to open the appropriate record in the second form....

I dont really get what do you mean by a sub-form with combos.

But if you want to open all related form for a certain record, you need to open all the forms. My suggestion is to combine all the forms into one (make a new form), so they will be a sub form. And just make a combo box in the header of the new form that will search for a certain record you type. Note that you must connect the forms with the same primary key.

 

Thanks for all the suggestions! I followed OBP's advice and made buttons then copies the code to the field control double click event! It worked like a dream!!!

I have one drawback.... if the field is empty, when I double click it gives me a "syntax error (missing operator) in query expression". I think that I could use some type of IF NULL statement in the code to open the form to a new record but it is beyond my skill set to manage on my own...

Here is the code:

Private Sub ProjectExpertName_DblClick(Cancel As Integer)
On Error GoTo Err_ProjectExpertName_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProjectExperts"

stLinkCriteria = "[ProjectExpertID]=" & Me![ProjectExpertID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ProjectExpertName_DblClick:
Exit Sub

Err_ProjectExpertName_DblClick:
MsgBox Err.Description
Resume Exit_ProjectExpertName_DblClick

End Sub

Could you please assist....?

As always, THANKS!!!!!

 

Before the
stDocName = "frmProjectExperts"
add the following code that says
If isnull(Me.[ProjectExpertID]) then
msgbox "You must enter something first"
exit sub
end if

 

OBP,

I would like for the action to pass to a new record if the user clicks in a blank field that opens the second form.....

they will have no criteria to place into a the system.... just the opportunity to add a new record to the second table....

can you help???

 

Isn't that what they can do on this form?

 

I need them to double click it a field on the first form and if the field is blank, for it to open a new record in the second form that opens...

Also, is there some way of limiting the function of a database for evaluation purposes? to not be able to save any added records on closing the evaluation copy????

Thanks!!!

 

Providing you are using Access 2003 you can use User Level security to set up "Read Only" users. I am not sure what Access 2007 allows, but you can provide duplicate "read only" forms which are selected using VBA.

If they open a new Blank Record what will be the Linking ID value?
As you will not have a ProjectExpertID

 

OBP,

Could I use a simple IF NULL/FALSE??? then open a new record in the second form??????

just trying to get around the error.....

 

Is the second form based on the same table as the first form?

 

I have a many-to-many relationship with a subform based on the junction table and would like to add to be able to double click and open the second form in the many to many. But it gives me the syntax error if the field is empty. If there is a record in the field it works fine.

Is there some if script that can be used to open into a new record in the second table if the field of origin is empty???

 

What is the Master/Child link or One to many Relationship fields?

 

they are all Many-to-Many or Parent / Child with the Junction table/subform has 2 combos set to each of the appropriate tables. This way I gather the related data between the 2 parent tables for further use in queries and reports.

 

But what field relates the Open Form to the form that you want to open with a new record?
If it is the ProjectExpertID you can't relate the new record to anything on the current form.
The vba to open the form on a new record it is easy enough, but when you create the new record what will you do with it?

 

The second form is related to the first by a many-to-many relation with a subform based on the junction table in the first showing a combo drop down of the ID from the second.

I don't need to relate the new record to anything in this moment. The double click to open the second form is only as a type of more information of record editing.

I just need a solution that will send the open form to a blank record if the field they click on in the first table is blank...

do you understand????

 

OK, here is the code to replace the message box

DoCmd.OpenForm "frmProjectExperts", , , , acFormAdd

However it does not always open in add mode, so we may have to go to a new record after the form has opened.

 

I have another problem...

I am trying to print a report for a single record based on a form:

Private Sub btnPrintReport_Click()
On Error GoTo Err_btnPrintReport_Click

Dim stDocName As String

stDocName = "rptOrganisationRecordPrint"
DoCmd.OutputTo acOutputReport, "Organisation Record Report_" & Me.OrganisationAcronym


Exit_btnPrintReport_Click:
Exit Sub

Err_btnPrintReport_Click:
MsgBox Err.Description
Resume Exit_btnPrintReport_Click

End Sub

It give me the choice of formats and selection of location, but when I click save, it shows me:

Microsoft office cannot find the object '|1'

Any ideas how to make it save the report???

Thanks!!!!!!!!

 

I have another problem...

I am trying to print a report for a single record based on a form:

Private Sub btnPrintReport_Click()
On Error GoTo Err_btnPrintReport_Click

Dim stDocName As String

stDocName = "rptOrganisationRecordPrint"
DoCmd.OutputTo acOutputReport, "Organisation Record Report_" & Me.OrganisationAcronym

Exit_btnPrintReport_Click:
Exit Sub

Err_btnPrintReport_Click:
MsgBox Err.Description
Resume Exit_btnPrintReport_Click

End Sub

It give me the choice of formats and selection of location, but when I click save, it shows me:

Microsoft office cannot find the object '|1'

Any ideas how to make it save the report???

Thanks!!!!!!!!

My code is telling Access to look for the name of the report as:
DoCmd.OutputTo acOutputReport, "Organisation Record Report_" & Me.OrganisationAcronym

HELP! PLEASE!!!

 

ok, here is the code to replace the message box

docmd.openform "frmprojectexperts", , , , acformadd

however it does not always open in add mode, so we may have to go to a new record after the form has opened.

works great!!!

Thanks!!!!!

 

The code that you have is not for printing a Report, it is for outputting it to a file.
I always use a Query to Filter the data for a Report using the Criteria
Forms![formname]![fieldname]

where formname is the name of your form and fieldname is the name of the field which you want to filter by (usually the key ID field).

 

The code that you have is not for printing a Report, it is for outputting it to a file.
I always use a Query to Filter the data for a Report using the Criteria
Forms![formname]![fieldname]

where formname is the name of your form and fieldname is the name of the field which you want to filter by (usually the key ID field).

Yeah, wrong work...was thinking print but meant Save As... Idealistically in PDF???? Can you help????

 

My code is telling Access to look for the name of the report as:
DoCmd.OutputTo acOutputReport, "Organisation Record Report_" & Me.OrganisationAcronym

HELP! PLEASE!!!

Correction! I am trying to Output to another format and have the name placed automatically in the Save As....

 

I would use a variable to combine the Organisation Record Report and the Me.OrganisationAcronym
ie
Dim fname as string
fname = "Organisation Record Report_" & Me.OrganisationAcronym
in that way you can check the fname with
msgbox fname.
You then need to add the output format types, see VBA Editor help for docmd.outputto
like this
DoCmd.OutputTo acOutputTable, "Employees", _
acFormatRTF, "Employee.rtf", True

 

I would use a variable to combine the Organisation Record Report and the Me.OrganisationAcronym
ie
Dim fname as string
fname = "Organisation Record Report_" & Me.OrganisationAcronym
in that way you can check the fname with
msgbox fname.
You then need to add the output format types, see VBA Editor help for docmd.outputto
like this
DoCmd.OutputTo acOutputTable, "Employees", _
acFormatRTF, "Employee.rtf", True

could you give me a step by step.....maybe baby steps...sorry!

 

OBP,

Thank you for all of your help!!!!!!!!!

I found this code that copies the report and should change the name to specified.....

Private Sub btnReportToFile_Click()
On Error GoTo Err_btnReportToFile_Click

Dim stDocName As String
stDocName = "rptProgrammeRecordPrint"
DoCmd.CopyObject , "Programme Record " & Format(Me!ProgrammeAcronym), acReport, "rptProgrammeRecordPrint"
DoCmd.OpenReport "Programme Record " & Format(Me!ProgrammeAcronym), acViewNormal
DoCmd.OutputTo acReport, stDocName

On Error Resume Next
DoCmd.DeleteObject acReport, "Programme Record " & Format(Me!ProgrammeAcronym)
On Error GoTo 0


Exit_btnReportToFile_Click:
Exit Sub

Err_btnReportToFile_Click:
MsgBox Err.Description
Resume Exit_btnReportToFile_Click

End Sub



But as YOU may see....it is not working....

I just want to output the report with a name I specify + the value of a field from the report, for example: Programme Report + ProgrammeAcronym

it seems hopeless....