Using Array----Please Help

[nxqviet]

Hi all,

I have a question about using array that loop through a query and using
it to check againts a field on a form here is my code:


Dim Source As DAO.Recordset
Set Source = CurrentDb().OpenRecordset("qryArrayName")
Dim arName() As String
Dim intNameCount() As Integer
intNameCount = qryArrayName.RecordCount
ReDim arName(intNameCount )

Source.MoveFirst

Dim i As Integer
For i = 1 To Source.RecordCount
arName(i) = Source!PackageName

If FieldName.Value = arName(i) Then
MsgBox "This Name Already Existed, Please Use a
Different Name"
Exit Sub
End If

Source.MoveNext

Next i

AddDetail '<----This is another Sub that have other action.


Basically, I want the Code to check the value of the field and see if
it already existed or not. If yes, then promt a message, IF NOT, then
run the "AddDetail " Sub.

This code contains some error, But I can't figure it out. Please help.
thanks.


V_

 

[nxqviet]

ok, I found an mistake in line 4 already,

Dim intNameCount() As Integer ------ Should be ------ Dim
intNameCount As Integer

Can you see anything else? thanks


V_

 

[Douglas J. Steele]

A few things to check:

RecordCount will not be accurate until you've gone to the end of the
recordset. That means you need a MoveLast before checking RecordCount.

You've got the line

intNameCount = qryArrayName.RecordCount

What is qryArrayName? (It's not defined anywhere in your code). Did you
perhaps mean to reference Source there? If so,

Dim Source As DAO.Recordset
Dim arName() As String
Dim intNameCount As Integer
Dim i As Integer

Set Source = CurrentDb().OpenRecordset("qryArrayName")
Source.MoveLast
intNameCount = Source.RecordCount
ReDim arName(intNameCount )

For i = 1 To intNameCount
arName(i) = Source!PackageName
If FieldName.Value = arName(i) Then
MsgBox "This Name Already Existed, " & _
"Please Use a Different Name."
Exit Sub
End If
Source.MoveNext
Next i

AddDetail

Also, what's FieldName? If it's a control on your form, you might be better
off using Me.FieldName.Text (using the Value property requires that the
control have focus)

Having said that, there's nothing in the code you've posted that requires
the use of an array, or do you use arName elsewhere in the code? It would
probably be better simply to have a query that checks whether the value in
FieldName exists, or use DCount or DLookup:

Dim intNameCount As Integer

intNameCount = DCount("*", "qryArrayName", _
"PackageName = '" & Me.FieldName.Text & "'")

If intNameCount > 0 Then
MsgBox "This Name Already Existed, " & _
"Please Use a Different Name."
Exit Sub
End If

AddDetail

or

If IsNull(DLookup("PackageName", "qryArrayName", _
"PackageName = '" & Me.FieldName.Text & "'")) = False Then
MsgBox "This Name Already Existed, " & _
"Please Use a Different Name."
Exit Sub
End If

AddDetail

 

[RoyVidar]

Hi all,

I have a question about using array that loop through a query and
using it to check againts a field on a form here is my code:


Dim Source As DAO.Recordset
Set Source = CurrentDb().OpenRecordset("qryArrayName")
Dim arName() As String
Dim intNameCount() As Integer
intNameCount = qryArrayName.RecordCount
ReDim arName(intNameCount )

Source.MoveFirst

Dim i As Integer
For i = 1 To Source.RecordCount
arName(i) = Source!PackageName

If FieldName.Value = arName(i) Then
MsgBox "This Name Already Existed, Please Use a
Different Name"
Exit Sub
End If

Source.MoveNext

Next i

AddDetail '<----This is another Sub that have other action.


Basically, I want the Code to check the value of the field and see if
it already existed or not. If yes, then promt a message, IF NOT, then
run the "AddDetail " Sub.

This code contains some error, But I can't figure it out. Please
help. thanks.


V_

First, if your object is to check if a value exists in a table, then I
don't understand why you're opening a recordset with all the records.

You could use domain aggregates, as demonstrated by Doug Steele, or
perhaps open the recordset with a where clause

Dim Source As DAO.Recordset
Dim strSql as string

strSql = "SELECT * FROM qryArrayName " & _
"WHERE PackageName = '" & Me!FieldName.Value & "'"

' Alternative WHERE clause, if it can contain single quotes
' "WHERE PackageName = '" & _
' Replace(Me!FieldName.Value, "'", "''") & "'"

Set Source = CurrentDb.OpenRecordset(strSql)
If Source.RecordCount > 0 Then
MsgBox "This Name Already Existed, " & _
"Please Use a Different Name."
Else
AddDetail
End If

 

[RoyVidar]

message <esqU#[email protected]>:

It's probably just a typo, but

Also, what's FieldName? If it's a control on your form, you might be
better off using Me.FieldName.Text (using the Value property requires
that the control have focus)

it's the other way around. Usage of the .Text property requires the
control to have focus. The .Value property (which is the default
property of controls) does not.

 

[Douglas J. Steele]

<esqU#[email protected]>:

It's probably just a typo, but


it's the other way around. Usage of the .Text property requires the
control to have focus. The .Value property (which is the default
property of controls) does not.

Right you are. I should probably wait until I'm been up a bit longer before
posting in the morning! <g>

 

[nxqviet]

Douglas ,

Thanks for your help, I'll try the Dlookup appoarch first and see if I
have any luck with that. The reason I choose to use array is because I
use array extendsively in Excel...but it is a bit different in Access,
I keep running into errors.

Thanks, I'll post follow-up question if I have any more trouble.


V_

 

[nxqviet]

Douglas,

The simple DCount procedures works perfectly, Thanks alot.


V_