LOOKUP FOR A VALUE ON A TABLE

[Samora]

Good evening everyone

I need some help

i have 3 tables: Header, Detail and Footer

The Detail table among other fields have 3 fields that i
have to manage:

The problem i have is this one

while i am writing the records and after i update field 1
and field 2 (both of them written) i have to lookup on my
Detail table to see if the field 1 concatenated with field
2 allready exists. if it exists, i want to obtain the
field 3 .

What is the purpose of this?

Field 1 Field 2 Field 3
1234 AP 0001
4567 AA 0002
6789 AA 0003
1234 AP 0001 <-----

As you can see on line 4 , the field 3 must have the value
0001 (because Field 1 concatenated with Field 2 allready
exists) instead of 0004 as it was supposed to be.

If someone can help me, i would appreciate very much

Samora

 

[Steve Schapel]

Samora,

You could try code something like this (warning: untested!) on the After
Update event of Field 2...

Dim Previous3 As String
Dim Next3 As String
Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1
& "' And [Field 2]='" & Me.Field_2 & "'"),"")
Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000")
If Len(Previous3) Then
Me.Field_3 = Previous3
Else
Me.Field_3 = Next3
End if

 

[Samora]

hi Steve

what do you mean by field 3 and field_3 ??
the same thing with Field 2 and field_2 and field 1 and
field_1 ? where are the differences ???

i have to replace them by my variables but you appear with
another variables that i didn't understand quiet clear...

According to this , my variables name are

Field 1 NumUniBen String
Field 2 Sigla String
Field 3 NumDoc String

do you think the code you sent to me is still OK???

Thanks for your help

Samora

-----Original Message-----
Samora,

You could try code something like this (warning: untested!) on the After
Update event of Field 2...

Dim Previous3 As String
Dim Next3 As String
Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1
& "' And [Field 2]='" & Me.Field_2 & "'"),"")
Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000")
If Len(Previous3) Then
Me.Field_3 = Previous3
Else
Me.Field_3 = Next3
End if

--
Steve Schapel, Microsoft Access MVP

Good evening everyone

I need some help

i have 3 tables: Header, Detail and Footer

The Detail table among other fields have 3 fields that i
have to manage:

The problem i have is this one

while i am writing the records and after i update field 1
and field 2 (both of them written) i have to lookup on my
Detail table to see if the field 1 concatenated with field
2 allready exists. if it exists, i want to obtain the
field 3 .

What is the purpose of this?

Field 1 Field 2 Field 3
1234 AP 0001
4567 AA 0002
6789 AA 0003
1234 AP 0001 <-----

As you can see on line 4 , the field 3 must have the value
0001 (because Field 1 concatenated with Field 2 allready
exists) instead of 0004 as it was supposed to be.

If someone can help me, i would appreciate very much

Samora

.

 

[Samora]

Hello Steve

Once again i think the code is working, but with one problem.

it retrieves for me the higher value on Field 3.

it is not what i want.

what i really want is:

if Field 1 concatenated with field 2 exists then it puts
on my field 3 the text that is on field 3 . the first one
it mets.

as you can see on my example, it was supposed to be the
number 0004. but as field 1 and field 2 allready exists on
my table i have to put there 0001 as you can see on my
first line.

i think now i explained everything for you

Once again, thanks for your help

-----Original Message-----


hi Steve

what do you mean by field 3 and field_3 ??
the same thing with Field 2 and field_2 and field 1 and
field_1 ? where are the differences ???

i have to replace them by my variables but you appear with
another variables that i didn't understand quiet clear...

According to this , my variables name are

Field 1 NumUniBen String
Field 2 Sigla String
Field 3 NumDoc String

do you think the code you sent to me is still OK???

Thanks for your help

Samora

-----Original Message-----
Samora,

You could try code something like this (warning: untested!) on the After
Update event of Field 2...

Dim Previous3 As String
Dim Next3 As String
Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1
& "' And [Field 2]='" & Me.Field_2 & "'"),"")
Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000")
If Len(Previous3) Then
Me.Field_3 = Previous3
Else
Me.Field_3 = Next3
End if

--
Steve Schapel, Microsoft Access MVP

Good evening everyone

I need some help

i have 3 tables: Header, Detail and Footer

The Detail table among other fields have 3 fields that i
have to manage:

The problem i have is this one

while i am writing the records and after i update field 1
and field 2 (both of them written) i have to lookup on my
Detail table to see if the field 1 concatenated with field
2 allready exists. if it exists, i want to obtain the
field 3 .

What is the purpose of this?

Field 1 Field 2 Field 3
1234 AP 0001
4567 AA 0002
6789 AA 0003
1234 AP 0001 <-----

As you can see on line 4 , the field 3 must have the value
0001 (because Field 1 concatenated with Field 2 allready
exists) instead of 0004 as it was supposed to be.

If someone can help me, i would appreciate very much

Samora

.

.

 

[Steve Schapel]

Samora,

Now it's me who is confused... What are the names of your fields? What
do you mean by "variables" in this context? I thought you had 3 fields
called Field 1, Field 2, and Field 3. If you have a space in the name
of a field or control, Access VBA intellisense will replace the space by
a _ in the code, hence the Field_1 etc in the example I gave you.

 

[Steve Schapel]

Samora,

I am not sure what is going wrong here... I think I understand the
desired outcome, and this is exactly what the example procedure I gave
you is designed to do. It should only assign the next higher number if
there is no previous instance of the Field 1 and Field 2 values.

 

[Steve Schapel]

Samora,

However, I do see an omission in my code... I forgot to add 1 to the
existing maximum Field 3 value. Should be like this...

Dim Previous3 As String
Dim Next3 As String
Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1
& "' And [Field 2]='" & Me.Field_2 & "'"),"")
Next3 = Format(Val(DMax("[Field 3]","Detail"))+1,"0000")
If Len(Previous3) Then
Me.Field_3 = Previous3
Else
Me.Field_3 = Next3
End if

--
Steve Schapel, Microsoft Access MVP

Hello Steve

Once again i think the code is working, but with one problem.

it retrieves for me the higher value on Field 3.

it is not what i want.

what i really want is:

if Field 1 concatenated with field 2 exists then it puts
on my field 3 the text that is on field 3 . the first one
it mets.

as you can see on my example, it was supposed to be the
number 0004. but as field 1 and field 2 allready exists on
my table i have to put there 0001 as you can see on my
first line.

i think now i explained everything for you

Once again, thanks for your help

-----Original Message-----


hi Steve

what do you mean by field 3 and field_3 ??
the same thing with Field 2 and field_2 and field 1 and
field_1 ? where are the differences ???

i have to replace them by my variables but you appear with
another variables that i didn't understand quiet clear...

According to this , my variables name are

Field 1 NumUniBen String
Field 2 Sigla String
Field 3 NumDoc String

do you think the code you sent to me is still OK???

Thanks for your help

Samora

-----Original Message-----
Samora,

You could try code something like this (warning:

untested!) on the After

Update event of Field 2...

Dim Previous3 As String
Dim Next3 As String
Previous3 = Nz(DLookup("[Field 3]","Detail","[Field

1]='" & Me.Field_1

& "' And [Field 2]='" & Me.Field_2 & "'"),"")
Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000")
If Len(Previous3) Then
Me.Field_3 = Previous3
Else
Me.Field_3 = Next3
End if

--
Steve Schapel, Microsoft Access MVP


Samora wrote:

Good evening everyone

I need some help

i have 3 tables: Header, Detail and Footer

The Detail table among other fields have 3 fields that i
have to manage:

The problem i have is this one

while i am writing the records and after i update field 1
and field 2 (both of them written) i have to lookup on my
Detail table to see if the field 1 concatenated with field
2 allready exists. if it exists, i want to obtain the
field 3 .

What is the purpose of this?

Field 1 Field 2 Field 3
1234 AP 0001
4567 AA 0002
6789 AA 0003
1234 AP 0001 <-----

As you can see on line 4 , the field 3 must have the value
0001 (because Field 1 concatenated with Field 2 allready
exists) instead of 0004 as it was supposed to be.

If someone can help me, i would appreciate very much

Samora




.

.

 

[Guest]

Hi Steve..

Thanks for your great help.

It is not necessary to add 1 to the maximum field 3.

The only problem i have is :

When there is no records with the Field 1 and the Field 2 ,
Field 3 must have "0000" in order for the user place the
number he wants.

Of course , your solution of adding 1 to the maximum field
is Ok and would releases a lot of work for the user ( i
could not make a tab stop to guarantee the value of the
Field 3).


However, i really would like to know your opinion about this.

Thanks in advance.

Samora

-----Original Message-----
Samora,

However, I do see an omission in my code... I forgot to add 1 to the
existing maximum Field 3 value. Should be like this...

Dim Previous3 As String
Dim Next3 As String
Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1
& "' And [Field 2]='" & Me.Field_2 & "'"),"")
Next3 = Format(Val(DMax("[Field 3]","Detail"))+1,"0000")
If Len(Previous3) Then
Me.Field_3 = Previous3
Else
Me.Field_3 = Next3
End if

--
Steve Schapel, Microsoft Access MVP

Hello Steve

Once again i think the code is working, but with one problem.

it retrieves for me the higher value on Field 3.

it is not what i want.

what i really want is:

if Field 1 concatenated with field 2 exists then it puts
on my field 3 the text that is on field 3 . the first one
it mets.

as you can see on my example, it was supposed to be the
number 0004. but as field 1 and field 2 allready exists on
my table i have to put there 0001 as you can see on my
first line.

i think now i explained everything for you

Once again, thanks for your help

-----Original Message-----


hi Steve

what do you mean by field 3 and field_3 ??
the same thing with Field 2 and field_2 and field 1 and
field_1 ? where are the differences ???

i have to replace them by my variables but you appear with
another variables that i didn't understand quiet clear...

According to this , my variables name are

Field 1 NumUniBen String
Field 2 Sigla String
Field 3 NumDoc String

do you think the code you sent to me is still OK???

Thanks for your help

Samora

-----Original Message-----
Samora,

You could try code something like this (warning:

untested!) on the After

Update event of Field 2...

Dim Previous3 As String
Dim Next3 As String
Previous3 = Nz(DLookup("[Field 3]","Detail","[Field

1]='" & Me.Field_1

& "' And [Field 2]='" & Me.Field_2 & "'"),"")
Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000")
If Len(Previous3) Then
Me.Field_3 = Previous3
Else
Me.Field_3 = Next3
End if

--
Steve Schapel, Microsoft Access MVP


Samora wrote:

Good evening everyone

I need some help

i have 3 tables: Header, Detail and Footer

The Detail table among other fields have 3 fields that i
have to manage:

The problem i have is this one

while i am writing the records and after i update field 1
and field 2 (both of them written) i have to lookup on my
Detail table to see if the field 1 concatenated with field
2 allready exists. if it exists, i want to obtain the
field 3 .

What is the purpose of this?

Field 1 Field 2 Field 3
1234 AP 0001
4567 AA 0002
6789 AA 0003
1234 AP 0001 <-----

As you can see on line 4 , the field 3 must have the value
0001 (because Field 1 concatenated with Field 2 allready
exists) instead of 0004 as it was supposed to be.

If someone can help me, i would appreciate very much

Samora




.


.

.

 

[Steve Schapel]

Samora,

I am sorry, I am not sure that I understand your question now. I don't
know what you mean about the "tab stop".

What I was trying to do was set things up so that when the user makes a
new entry in field 1 and field 2, the value of field 3 is automatically
entered for you. If there is already a record with the same field 1 and
field 2 values, field 3 will show the same as the field 3 vlaue of the
previous matching record. If there is no previous matching record,
field 3 will have the next sequential number entered automatically. I
thought that was what you wanted. That's the way I would do it if it
was mine

 

[Samora]

Hi Steve..

everything is running OK..

Thanks for your help...

i posted a new problem...named MAIL - COMMAND or ACTIVEX.

Do you think you can help me on that one???

Thanks for your kindly help

Best regards

Samora