Trouble with my VB Code

B

Bob Vance

I can get the first part of my code to work when the Combo box cbHorseName
is empty I get the Message but I cant get cbHorseName & List Box lbOwnerlist
to work, actually lbOwnerList wont even work it self......Thanks Bob
Private Sub Form_Load()
DoCmd.Maximize

On Error Resume Next
tbCompanyName.value = DLookup("CompanyName", "tblCompanyInfo")

If cbHorseName = 0 Then
MsgBox "(Client Invoice) Must be Edited from Main
Menu/Client Invoices! "
If cbHorseName = 0 & lbOwnerList = 0 Then
MsgBox "(No Owner) ! "
End If
End If
End Sub
 
J

JvC

Bob,

I suspect that cboHorseName and lbOwnerList are null, rather than 0, so
your code never runs. If you want to check for zero, you will need to
set the default for the fields to 0, and then run a query to update
them. The other thing you could do is check If IsNull(cboHorseName)
then, etc.

John

Bob Vance expressed precisely :
 
S

Steve Sanford

Hi Bob,

Here are some of my thoughts and observsations:

"Value" is the default property for a textbox (as in tbCompanyName.value)...
you do not have to have it (saves typing).
------------
It looks like you have at combo box named "cbHorseName" and a list box named
"lbOwnerList". Is the bound column for the combo box and the list box a long
integer (and the PK)? Or is the bound column a string?

If the bound column is an integer, to check if a horse name was selected, I
would use:

If Nz(cbHorseName,0) = 0 Then

The function Nz() (Null to Zero) converts a Null to (in this case) a 0 (Zero)

If the bound column is an string, to check if a horse name was selected, I
would use:

If Len(Nz(cbHorseName,"")) = 0 Then

Here I am using the Nz() function to convert a Null to an empty string, then
using the Len() function to check the length of the variable value.
------------
If cbHorseName = 0 & lbOwnerList = 0 Then
Click to expand...

In this (nested) If() function, you are using a string concatenation
operator (&) between the variables instead of the "And" operator. The
statement should be:

If cbHorseName = 0 AND lbOwnerList = 0 Then

Also, the first expression, "cbHorseName = 0", is not necessary, because you
already checked for the HorseName in the outer If() function...(but it
doesn't hurt anything).

Note that if a horse name was selected, the check for the owner name
(lbOwnerList = 0) would never occur.
------------
tbCompanyName.value = DLookup("CompanyName", "tblCompanyInfo")
Click to expand...

The syntax for the DLookup() function is: DLookup(expr, domain, [criteria])
While the criteria caluse is optional, this is what help says:

"The DLookup function returns a single field value based on the information
specified in criteria. Although criteria is an optional argument, *if you
don't supply a value for criteria, the DLookup function returns a random
value in the domain.*"

Unless you have only one company name in the table, using the DLookup()
function like you have it will result in a random company name being entered
in the [tbCompanyName] control.
------------

Here are two versions of your code:

'--- VERSION 1 ---
Private Sub Form_Load()
DoCmd.Maximize

On Error Resume Next

' ------ this needs a criteria clause
Me.tbCompanyName = DLookup("CompanyName", "tblCompanyInfo")
'-------

If Nz(Me.cbHorseName,0) = 0 Then
MsgBox "(Client Invoice) Must be Edited from Main Menu/Client
Invoices! "
If Nz(Me.lbOwnerList,0) = 0 Then
MsgBox "(No Owner) ! "
End If
End If
End Sub


'--- VERSION 2---
Private Sub Form_Load()
DoCmd.Maximize

On Error Resume Next

' ------ this needs a criteria clause
Me.tbCompanyName = DLookup("CompanyName", "tblCompanyInfo")
'-------

' check for horse name
If Nz(Me.cbHorseName,0) = 0 Then
MsgBox "(Client Invoice) Must be Edited from Main Menu/Client
Invoices! "
'check for owner name
ElseIf Nz(Me.lbOwnerList,0) = 0 Then
MsgBox "(No Owner) ! "
End If
End Sub

'--------------------

I can't help but wonder why this code is in the form load event. If the form
is used to enter new records, won't [cbHorseName] and [lbOwnerList] always be
NULL? If the form has a recordsource, won't the [cbHorseName] and
[lbOwnerList] have values?


HTH
 
B

Bob Vance

Steve BRILLIANT thank you for your effort your version 2 worked perfect
these are records already saved hence the No names in either combo or list
box
Couple of little bugs if you change the owner of a horse his old Invoice to
edit will not have his name, and the Owner Invoices are different as they do
not go through the horses name so need to alert the person opening the
Invoices to edit, Brilliant thanks Bob

Steve Sanford said:
Hi Bob,

Here are some of my thoughts and observsations:

"Value" is the default property for a textbox (as in
tbCompanyName.value)...
you do not have to have it (saves typing).
------------
It looks like you have at combo box named "cbHorseName" and a list box
named
"lbOwnerList". Is the bound column for the combo box and the list box a
long
integer (and the PK)? Or is the bound column a string?

If the bound column is an integer, to check if a horse name was selected,
I
would use:

If Nz(cbHorseName,0) = 0 Then

The function Nz() (Null to Zero) converts a Null to (in this case) a 0
(Zero)

If the bound column is an string, to check if a horse name was selected, I
would use:

If Len(Nz(cbHorseName,"")) = 0 Then

Here I am using the Nz() function to convert a Null to an empty string,
then
using the Len() function to check the length of the variable value.
------------
If cbHorseName = 0 & lbOwnerList = 0 Then
Click to expand...

In this (nested) If() function, you are using a string concatenation
operator (&) between the variables instead of the "And" operator. The
statement should be:

If cbHorseName = 0 AND lbOwnerList = 0 Then

Also, the first expression, "cbHorseName = 0", is not necessary, because
you
already checked for the HorseName in the outer If() function...(but it
doesn't hurt anything).

Note that if a horse name was selected, the check for the owner name
(lbOwnerList = 0) would never occur.
------------
tbCompanyName.value = DLookup("CompanyName", "tblCompanyInfo")
Click to expand...

The syntax for the DLookup() function is: DLookup(expr, domain,
[criteria])
While the criteria caluse is optional, this is what help says:

"The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional argument, *if you
don't supply a value for criteria, the DLookup function returns a random
value in the domain.*"

Unless you have only one company name in the table, using the DLookup()
function like you have it will result in a random company name being
entered
in the [tbCompanyName] control.
------------

Here are two versions of your code:

'--- VERSION 1 ---
Private Sub Form_Load()
DoCmd.Maximize

On Error Resume Next

' ------ this needs a criteria clause
Me.tbCompanyName = DLookup("CompanyName", "tblCompanyInfo")
'-------

If Nz(Me.cbHorseName,0) = 0 Then
MsgBox "(Client Invoice) Must be Edited from Main Menu/Client
Invoices! "
If Nz(Me.lbOwnerList,0) = 0 Then
MsgBox "(No Owner) ! "
End If
End If
End Sub


'--- VERSION 2---
Private Sub Form_Load()
DoCmd.Maximize

On Error Resume Next

' ------ this needs a criteria clause
Me.tbCompanyName = DLookup("CompanyName", "tblCompanyInfo")
'-------

' check for horse name
If Nz(Me.cbHorseName,0) = 0 Then
MsgBox "(Client Invoice) Must be Edited from Main Menu/Client
Invoices! "
'check for owner name
ElseIf Nz(Me.lbOwnerList,0) = 0 Then
MsgBox "(No Owner) ! "
End If
End Sub

'--------------------

I can't help but wonder why this code is in the form load event. If the
form
is used to enter new records, won't [cbHorseName] and [lbOwnerList] always
be
NULL? If the form has a recordsource, won't the [cbHorseName] and
[lbOwnerList] have values?


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Bob Vance said:
I can get the first part of my code to work when the Combo box
cbHorseName
is empty I get the Message but I cant get cbHorseName & List Box
lbOwnerlist
to work, actually lbOwnerList wont even work it self......Thanks Bob
Private Sub Form_Load()
DoCmd.Maximize

On Error Resume Next
tbCompanyName.value = DLookup("CompanyName", "tblCompanyInfo")

If cbHorseName = 0 Then
MsgBox "(Client Invoice) Must be Edited from Main
Menu/Client Invoices! "
If cbHorseName = 0 & lbOwnerList = 0 Then
MsgBox "(No Owner) ! "
End If
End If
End Sub
Click to expand...
Click to expand...
 
S

Steve Sanford

You're welcome...
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Bob Vance said:
Steve BRILLIANT thank you for your effort your version 2 worked perfect
these are records already saved hence the No names in either combo or list
box
Couple of little bugs if you change the owner of a horse his old Invoice to
edit will not have his name, and the Owner Invoices are different as they do
not go through the horses name so need to alert the person opening the
Invoices to edit, Brilliant thanks Bob

Steve Sanford said:
Hi Bob,

Here are some of my thoughts and observsations:

"Value" is the default property for a textbox (as in
tbCompanyName.value)...
you do not have to have it (saves typing).
------------
It looks like you have at combo box named "cbHorseName" and a list box
named
"lbOwnerList". Is the bound column for the combo box and the list box a
long
integer (and the PK)? Or is the bound column a string?

If the bound column is an integer, to check if a horse name was selected,
I
would use:

If Nz(cbHorseName,0) = 0 Then

The function Nz() (Null to Zero) converts a Null to (in this case) a 0
(Zero)

If the bound column is an string, to check if a horse name was selected, I
would use:

If Len(Nz(cbHorseName,"")) = 0 Then

Here I am using the Nz() function to convert a Null to an empty string,
then
using the Len() function to check the length of the variable value.
------------
If cbHorseName = 0 & lbOwnerList = 0 Then
Click to expand...

In this (nested) If() function, you are using a string concatenation
operator (&) between the variables instead of the "And" operator. The
statement should be:

If cbHorseName = 0 AND lbOwnerList = 0 Then

Also, the first expression, "cbHorseName = 0", is not necessary, because
you
already checked for the HorseName in the outer If() function...(but it
doesn't hurt anything).

Note that if a horse name was selected, the check for the owner name
(lbOwnerList = 0) would never occur.
------------
tbCompanyName.value = DLookup("CompanyName", "tblCompanyInfo")
Click to expand...

The syntax for the DLookup() function is: DLookup(expr, domain,
[criteria])
While the criteria caluse is optional, this is what help says:

"The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional argument, *if you
don't supply a value for criteria, the DLookup function returns a random
value in the domain.*"

Unless you have only one company name in the table, using the DLookup()
function like you have it will result in a random company name being
entered
in the [tbCompanyName] control.
------------

Here are two versions of your code:

'--- VERSION 1 ---
Private Sub Form_Load()
DoCmd.Maximize

On Error Resume Next

' ------ this needs a criteria clause
Me.tbCompanyName = DLookup("CompanyName", "tblCompanyInfo")
'-------

If Nz(Me.cbHorseName,0) = 0 Then
MsgBox "(Client Invoice) Must be Edited from Main Menu/Client
Invoices! "
If Nz(Me.lbOwnerList,0) = 0 Then
MsgBox "(No Owner) ! "
End If
End If
End Sub


'--- VERSION 2---
Private Sub Form_Load()
DoCmd.Maximize

On Error Resume Next

' ------ this needs a criteria clause
Me.tbCompanyName = DLookup("CompanyName", "tblCompanyInfo")
'-------

' check for horse name
If Nz(Me.cbHorseName,0) = 0 Then
MsgBox "(Client Invoice) Must be Edited from Main Menu/Client
Invoices! "
'check for owner name
ElseIf Nz(Me.lbOwnerList,0) = 0 Then
MsgBox "(No Owner) ! "
End If
End Sub

'--------------------

I can't help but wonder why this code is in the form load event. If the
form
is used to enter new records, won't [cbHorseName] and [lbOwnerList] always
be
NULL? If the form has a recordsource, won't the [cbHorseName] and
[lbOwnerList] have values?


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Bob Vance said:
I can get the first part of my code to work when the Combo box
cbHorseName
is empty I get the Message but I cant get cbHorseName & List Box
lbOwnerlist
to work, actually lbOwnerList wont even work it self......Thanks Bob
Private Sub Form_Load()
DoCmd.Maximize

On Error Resume Next
tbCompanyName.value = DLookup("CompanyName", "tblCompanyInfo")

If cbHorseName = 0 Then
MsgBox "(Client Invoice) Must be Edited from Main
Menu/Client Invoices! "
If cbHorseName = 0 & lbOwnerList = 0 Then
MsgBox "(No Owner) ! "
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
Stop Form from Opening 2
Intellisoft Problem! 7
Email Through Windows Live! 2
VBA works in orignal but fails in copy of database 1
Add to text box String 4
Case strFormat = acFormatRTF, Problem 5
Open Report while creating email 1

Top