[X]My Assistant

[Aakburns]

I have a database that shows a subform. I used the doubleclick event with a macro, so that the first column would open a form based on that rows information. I'd like the row that shows (new) to open a different form than the rows that have a record. Is there something I can add to the current macro to make (new) open a different form than the rest?

Thanks.

Attached File(s)

 datasheet_openforms.png ( 12.87K )Number of downloads: 0
 current_macro.png ( 14.48K )Number of downloads: 4

 

[theDBguy]

Hi. Welcome to UtterAccess!

Yes, you could try adding an If/Else macro action.

[Aakburns]

I did play around with that a bit, but wasn't sure what syntax to use. My attempts didn't work.

[theDBguy]

Try checking the Job_ID field. For example:

If IsNull([Job_ID])

[Aakburns]

Adding that as an if statement, allows the fields with numbers to still work without error, however, if I double click on (new) I get this error.
This post has been edited by Aakburns: Dec 4 2019, 04:15 PM

Attached File(s)

 click_new_error.png ( 8.63K )Number of downloads: 1
 macro_error.png ( 15.09K )Number of downloads: 1
 macro_with_if_statement.png ( 19.35K )Number of downloads: 4

 

[theDBguy]

Ah, you need to put the original code inside an Else branch of the If/Else block. In other words, it would read like this:

If IsNull() Then
Open New Form
Else
Open Old Form with ID=blah...
End If

[Aakburns]

If IsNull()

Cannot parse 'IsNull()'

[theDBguy]

Hi. You'd have to select an action from the dropdown. If you don't see IsNull(), try Nz().

[Aakburns]

Good Morning.

I tried changing it to Nz, which now, if you double click to open a job_id it will open and also open the second form. However, this is not what I'm looking for. Also, double clicking (New) gives me a syntax error still. "Syntax error (missing operator in query expression '[Job_ID='."

Arguments: frm_update job information, Form, , ="[Job_ID]=" & [Job_ID]
This post has been edited by Aakburns: Dec 5 2019, 10:25 AM

[theDBguy]

Hi. To help you fix it, are you able to post a small copy of your db?

[Aakburns]

There's sensitive data in it. Perhaps I can make a very slim table version with no data in it, I'll try and figure that out this weekend.

[cheekybuddha]

Alternatively, you could stop banging your head against the wall with macros and use some simple VBA instead:

CODE

Private Sub Job_ID_DblClick(Cancel As Integer)

  If Me.NewRecord Then
    DoCmd.OpenForm "frm_add new job information", , , , acFormAdd
  Else
    DoCmd.OpenForm "frm_update job information", , , "Job_ID = " & Me.Job_ID
  End If

End Sub

Change the name of the macro in the On Dbl Click property for Job_ID in the property sheet to [Event Procedure]

Then click the ellipsis (three little dots) to the right and you will be taken to the code editor where you can paste in the code.

Remember to change the name of your add new data dorm to the correct name.

(BTW, You probably don't need two different forms for adding/updating. You can open the updating form in acFormAdd mode instead.)

hth,

d

[Aakburns]

I ended up finding it to be a better option to disable allowing additions for the subform for now. I will however keep this in mind if I change it back.

Thanks for all the input.

[theDBguy]

Sounds good. Good luck with your project.