Question Check if field input value is in table colomn, else do nothing (1 Viewer)

bruceblack

Registered User.
Local time
Today, 13:34
Joined
Jun 30, 2017
Messages
119
Hi folks! I'm a little bit stuck on how to get this done. Probably because i dont know which term of function im looking for. Please help...

I have a table (table1), Colomn (colomn2) And i have a form with some input fields. inputfield1 and inputfield2

When i enter data into a field on the form, i would like Access to check if that value is listed in the colomn in my table.
If yes: post that value to another field on that form and empty current field.

If not listed: leave value as it is in that field.

Something along the lines of:


inputfield1_afterupdate
If inputfield1.value = in table1.colomn2 THEN inputfield2.value=inputfield1
DoCmd.Clear inputfield 1
Else "do nothing"


How can i do this?
I would like to do this in VBA in an after update event

Additional info:
(Im making a form where we can scan products, but if the value i enter happens to be listed as a location instead of a product, i want the value to be copied to the location field. If its not listed, it means that it concerns a product code, so no changes or actions are needed in that case)
 
Last edited:

Minty

AWF VIP
Local time
Today, 13:34
Joined
Jul 26, 2013
Messages
10,354
Your description and terminology is a little confusing - a field and a column are the same things in an access table.

So your description of table1.colomn2.field3 really makes no sense to me at all I'm afraid.

Would it be correct to assume you have two fields in your table?
 

bruceblack

Registered User.
Local time
Today, 13:34
Joined
Jun 30, 2017
Messages
119
Hi man! Thanks for you swift reply. Youre right, i edited my question. Thanks!
 

Ranman256

Well-known member
Local time
Today, 09:34
Joined
Apr 9, 2015
Messages
4,339
1st: Columns ARE fields. So you would not have:
table1.colomn2.field3
it would be: table1.field3

2: you can put a combo box. It has all the values from that field to pick.
if it is there, fill in the other boxes. If NOT IN LIST, dont do anything (or show warning using NOT IN LIST error property)

or with a textbox:
Code:
sub txtBox_afterupdate()
vVal = Dlookup("[field3]","table","[field]='" & txtBox  & "'")       'lookup item
if IsNull(vVal) then 
   'do nothing
else
 inputfield2.value=inputfield1
endif
end sub
 

bruceblack

Registered User.
Local time
Today, 13:34
Joined
Jun 30, 2017
Messages
119
Im so sorry. Im clumsy and in a hurry at the same time. Much appricated and im almost there. So simple it is really.

Im just a little bit confused with one of the values you put in:


sub txtBox_afterupdate()
vVal = Dlookup("[field3]","table","[field]='" & txtBox & "'") 'lookup item
if IsNull(vVal) then
'do nothing
else
inputfield2.value=inputfield1
endif
end sub


What exactly does that field mean?
field3 is the table field, then the table, and then....??
txtBox would be the field im in currently right?

Everthing else should be working now.
 

Ranman256

Well-known member
Local time
Today, 09:34
Joined
Apr 9, 2015
Messages
4,339
text box holds the value the user enters to see if it exists:

Dlookup("[field to return]","table","[LookupField]='" & txtBox & "'")

if it returns NULL, the item does not exist.
(you can alway use help to see definitions of all functions)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:34
Joined
May 7, 2009
Messages
19,169
you already have the pseducode there
why don't you just translate it.
use the Control's AfterUpdate Event:


Code:
Private Sub Inputfield1_AfterUpdate()

If DCount("*","table1","Column2=" & Chr(34) & [Inputfield1] & Chr(34))<>0 Then
	Me.InputField2 = [Inputfield1]
	Me.InputField2=Null
End If

End Sub
 

Minty

AWF VIP
Local time
Today, 13:34
Joined
Jul 26, 2013
Messages
10,354
I would go with the combo box input - limit the available input to the what can legitimately be input from the data already there. I do exactly this with a barcode scanning form.
 

bruceblack

Registered User.
Local time
Today, 13:34
Joined
Jun 30, 2017
Messages
119
Hah! Guys it works :). Thank you so much. Indeed it was textbox.

sub txtBox_afterupdate()
vVal = Dlookup("[field3]","table","[field]='" & txtBox & "'") 'lookup item
if IsNull(vVal) then
'do nothing
else
inputfield2.value=inputfield1
endif
end sub


So this principle was very sufficient and clean and simple.


As far as the else statement: inputfield2.value=inputfield1
This does work, if it were not that im working with subforms.

So inputfield1 = in subform 1
and inputfield2 = in subform 2

Any change how to adres this?
im using Me.inputfield1.Value = Me!Subform2name.Form!inputfield2

But this is not working. I tried serveral different ones but....
Other than that im more than happy.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:34
Joined
May 7, 2009
Messages
19,169
Me.Parent![subform2 name].Form!inputfield2=Me.inputfield1

Replace sumform2 name with correct name of subform
 

Ranman256

Well-known member
Local time
Today, 09:34
Joined
Apr 9, 2015
Messages
4,339
always use the BUILDER to get the path correct.
just pick your object from the list.
 

bruceblack

Registered User.
Local time
Today, 13:34
Joined
Jun 30, 2017
Messages
119
Thank you so much everyone! That last one did it completely. So nice of you.
Much appreciated
 

Users who are viewing this thread

Top Bottom