List box DblClick to open form when Null to new record

[d9pierce]

Hi group

I have a list box on my form that when double clicked it opens a form
to edit or add a new division. No biggie but when it is dbl clicked
with no data, it goes to error and debug. I need to know how to create
a code like an If Null or something to open this to a new record.

Private Sub List139_DblClick(Cancel As Integer)
DoCmd.OpenForm "Client_Division", , , "[CompanyID]=" & Me!
[List139].Column(0)

End Sub

List box was made from Query,
Column 1 = ClientDivisionID
Column 2 = CompanyID / Criteria: Forms!Company_Main!CompanyID
Column 3 = DivisionName

Bound Column 1

Thanks so much,
Dave

 

[Allen Browne]

Dim strWhere As String
If IsNull(Me.List139) Then
DoCmd.OpenForm "Client_Division", , , , acFormAdd
Else
strWhere = "[CompanyID]= " & Me![List139].Column(0)
DoCmd.OpenForm "Client_Division", , , strWhere
End If

 

[Naeem Azizian]

Hi back,
If isnull(list39) then
DoCmd.OpenForm "Client_Division"
else
DoCmd.OpenForm "Client_Division", , , "[CompanyID]=" & list39
end if

 

[Rick Brandt]

Hi group

I have a list box on my form that when double clicked it opens a form
to edit or add a new division. No biggie but when it is dbl clicked
with no data, it goes to error and debug. I need to know how to create
a code like an If Null or something to open this to a new record.

Private Sub List139_DblClick(Cancel As Integer)
DoCmd.OpenForm "Client_Division", , , "[CompanyID]=" & Me!
[List139].Column(0)

End Sub

List box was made from Query,
Column 1 = ClientDivisionID
Column 2 = CompanyID / Criteria: Forms!Company_Main!CompanyID
Column 3 = DivisionName

Bound Column 1

Thanks so much,
Dave

Assuming that there is no CompanyID that is zero...

DoCmd.OpenForm "Client_Division", , , "[CompanyID]=" & Nz(list39,0)

 

[d9pierce]

Dim strWhere As String
If IsNull(Me.List139) Then
DoCmd.OpenForm "Client_Division", , , , acFormAdd
Else
strWhere = "[CompanyID]= " & Me![List139].Column(0)
DoCmd.OpenForm "Client_Division", , , strWhere
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi group

I have a list box on my form that when double clicked it opens a form
to edit or add a new division. No biggie but when it is dbl clicked
with no data, it goes to error and debug. I need to know how to create
a code like an If Null or something to open this to a new record.

Private Sub List139_DblClick(Cancel As Integer)
DoCmd.OpenForm "Client_Division", , , "[CompanyID]=" & Me!
[List139].Column(0)

End Sub

List box was made from Query,
Column 1 = ClientDivisionID
Column 2 = CompanyID / Criteria: Forms!Company_Main!CompanyID
Column 3 = DivisionName

Bound Column 1

Thanks so much,
Dave- Hide quoted text -

- Show quoted text -

Hi and thanks to all, I tried all of these and couldnt get even one to
work. I keep getting syntax errors on all of them. Here is what I have
currently and it opens form when Not Null but when nothing is in the
listbox, it wont go to open form to add new. Any suggestions?
Thanks

Private Sub List139_DblClick(Cancel As Integer)
Dim strWhere As String
If IsNull(Me.List139) Then
DoCmd.OpenForm "Client_Division", , , , acFormAdd
Else
strWhere = "[CompanyID]= " & Me![List139].Column(0)
DoCmd.OpenForm "Client_Division", , , strWhere
End If
End Sub

 

[d9pierce]

Hi and thanks to all, I tried all of these and couldnt get even one to
work. I keep getting syntax errors on all of them. Here is what I have
currently and it opens form when Not Null but when nothing is in the
listbox, it wont go to open form to add new. Any suggestions?
Thanks

Private Sub List139_DblClick(Cancel As Integer)
Dim strWhere As String
If IsNull(Me.List139) Then
DoCmd.OpenForm "Client_Division", , , , acFormAdd
Else
strWhere = "[CompanyID]= " & Me![List139].Column(0)
DoCmd.OpenForm "Client_Division", , , strWhere
End If
End Sub

What is the error?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

Hi Rick,
Error is:
Runtime Error '3075';
Syntax error (missing operator) in Query Expression '[CompanyID]='

 

[Allen Browne]

The expression:
[CompanyID]=
is indeed incomplete.

The value you are concatenating from the list box must be a zero-length
string.

Test for that, and ensure you have a valid number to concatenate into the
string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi and thanks to all, I tried all of these and couldnt get even one to
work. I keep getting syntax errors on all of them. Here is what I have
currently and it opens form when Not Null but when nothing is in the
listbox, it wont go to open form to add new. Any suggestions?
Thanks

Private Sub List139_DblClick(Cancel As Integer)
Dim strWhere As String
If IsNull(Me.List139) Then
DoCmd.OpenForm "Client_Division", , , , acFormAdd
Else
strWhere = "[CompanyID]= " & Me![List139].Column(0)
DoCmd.OpenForm "Client_Division", , , strWhere
End If
End Sub

What is the error?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

Hi Rick,
Error is:
Runtime Error '3075';
Syntax error (missing operator) in Query Expression '[CompanyID]='

 

[d9pierce]

The expression:
[CompanyID]=
is indeed incomplete.

The value you are concatenating from the list box must be a zero-length
string.

Test for that, and ensure you have a valid number to concatenate into the
string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

(e-mail address removed) wrote:
Hi and thanks to all, I tried all of these and couldnt get even one to
work. I keep getting syntax errors on all of them. Here is what I have
currently and it opens form when Not Null but when nothing is in the
listbox, it wont go to open form to add new. Any suggestions?
Thanks
Private Sub List139_DblClick(Cancel As Integer)
Dim strWhere As String
If IsNull(Me.List139) Then
DoCmd.OpenForm "Client_Division", , , , acFormAdd
Else
strWhere = "[CompanyID]= " & Me![List139].Column(0)
DoCmd.OpenForm "Client_Division", , , strWhere
End If
End Sub
What is the error?
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -
- Show quoted text -

Hi Rick,
Error is:
Runtime Error '3075';
Syntax error (missing operator) in Query Expression '[CompanyID]='- Hide quoted text -

- Show quoted text -

Hi and thanks but I have no idea how to test for a zero lenght?

 

[Allen Browne]

=""

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The expression:
[CompanyID]=
is indeed incomplete.

The value you are concatenating from the list box must be a zero-length
string.

Test for that, and ensure you have a valid number to concatenate into the
string.

(e-mail address removed) wrote:
Hi and thanks to all, I tried all of these and couldnt get even one
to
work. I keep getting syntax errors on all of them. Here is what I
have
currently and it opens form when Not Null but when nothing is in
the
listbox, it wont go to open form to add new. Any suggestions?
Thanks

Private Sub List139_DblClick(Cancel As Integer)
Dim strWhere As String
If IsNull(Me.List139) Then
DoCmd.OpenForm "Client_Division", , , , acFormAdd
Else
strWhere = "[CompanyID]= " & Me![List139].Column(0)
DoCmd.OpenForm "Client_Division", , , strWhere
End If
End Sub

What is the error?

- Show quoted text -

Hi Rick,
Error is:
Runtime Error '3075';
Syntax error (missing operator) in Query Expression '[CompanyID]='-
Hide quoted text -

- Show quoted text -

Hi and thanks but I have no idea how to test for a zero lenght?

 

[Douglas J. Steele]

Or Len([CompanyId]) = 0

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

=""

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The expression:
[CompanyID]=
is indeed incomplete.

The value you are concatenating from the list box must be a zero-length
string.

Test for that, and ensure you have a valid number to concatenate into
the
string.





(e-mail address removed) wrote:
Hi and thanks to all, I tried all of these and couldnt get even one
to
work. I keep getting syntax errors on all of them. Here is what I
have
currently and it opens form when Not Null but when nothing is in
the
listbox, it wont go to open form to add new. Any suggestions?
Thanks

Private Sub List139_DblClick(Cancel As Integer)
Dim strWhere As String
If IsNull(Me.List139) Then
DoCmd.OpenForm "Client_Division", , , , acFormAdd
Else
strWhere = "[CompanyID]= " & Me![List139].Column(0)
DoCmd.OpenForm "Client_Division", , , strWhere
End If
End Sub

What is the error?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

Hi Rick,
Error is:
Runtime Error '3075';
Syntax error (missing operator) in Query Expression '[CompanyID]='-
Hide quoted text -

- Show quoted text -

Hi and thanks but I have no idea how to test for a zero lenght?