filling a field automatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database (table). e.g. part no vs part description.
Then I created a form that includes part no, part description, etc.
I don't want to enter the part description whenever I enter the part no. Is
there a method for this. (like a VLOOKUP in excel)
 
It called a DLookup in Access.

In the control source of the Iten Desc write
' If item number type is number
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = " & [Item Number Field
Name In the Form] )

' If item number type is text
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = '" & [Item Number Field
Name In the Form] & "'" )
 
Hi,

i assume you are using a combo box to select your part number.

if so then your combo box criteria would be 3 columns, width (0;3;0) where
column 1 is the part id, column 2 is the part number and column 3 is the
description

on the after update event on you from have the follwing code

me.txtPartDescription = me.cmbPartNumber.column(2)

assuming that cmbpartnumber is the name of your combo box

hope this helps

Lee-Anne
 
sorry that sould say

Hi,

i assume you are using a combo box to select your part number.

if so then your combo box criteria would be 3 columns, width (0;3;0) where
column 1 is the part id, column 2 is the part number and column 3 is the
description

on the after update event on you from have the follwing code

me.txtPartDescription = me.cmbPartNumber.column(2)

assuming that cmbpartnumber is the name of your combo box

hope this helps

Lee-Anne
Click to expand...
 
Thank you very much.
I enter the following expression. But it gave syntax error.
=Dlookup("[PARTDESC]","[DESC]","[PARTNO] = '" & [PARTNO] & "'" )
Then I enter the following expression by using help in expression builder.
=DLookup("[PARTDESC]", "DESC", "[PARTNO] =" _
& Forms![FORM]!PARTNO)
it gave syntax error too. What is wrong.
DESC: table name
PARTDESC: description's field name in the table DESC
PARTNO:number's field name in the table DESC
2nd PARTNO: field name of part no in the form.


Ofer said:
It called a DLookup in Access.

In the control source of the Iten Desc write
' If item number type is number
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = " & [Item Number Field
Name In the Form] )

' If item number type is text
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = '" & [Item Number Field
Name In the Form] & "'" )

--
I hope that helped
Good luck


AhmtDY said:
I have a database (table). e.g. part no vs part description.
Then I created a form that includes part no, part description, etc.
I don't want to enter the part description whenever I enter the part no. Is
there a method for this. (like a VLOOKUP in excel)
Click to expand...
Click to expand...
 
From the two example you gave, once you treated the Item No as string and
once as number

Try this for numeric field type
=Dlookup("[PARTDESC]","[DESC]","[PARTNO] = " & [PARTNO])

--
I hope that helped
Good luck


AhmtDY said:
Thank you very much.
I enter the following expression. But it gave syntax error.
=Dlookup("[PARTDESC]","[DESC]","[PARTNO] = '" & [PARTNO] & "'" )
Then I enter the following expression by using help in expression builder.
=DLookup("[PARTDESC]", "DESC", "[PARTNO] =" _
& Forms![FORM]!PARTNO)
it gave syntax error too. What is wrong.
DESC: table name
PARTDESC: description's field name in the table DESC
PARTNO:number's field name in the table DESC
2nd PARTNO: field name of part no in the form.


Ofer said:
It called a DLookup in Access.

In the control source of the Iten Desc write
' If item number type is number
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = " & [Item Number Field
Name In the Form] )

' If item number type is text
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = '" & [Item Number Field
Name In the Form] & "'" )

--
I hope that helped
Good luck


AhmtDY said:
I have a database (table). e.g. part no vs part description.
Then I created a form that includes part no, part description, etc.
I don't want to enter the part description whenever I enter the part no. Is
there a method for this. (like a VLOOKUP in excel)
Click to expand...
Click to expand...
Click to expand...
 
Ofer your expression run, when I changed ";" for ";" ie.
=Dlookup("[ItemDesc]";"[Table Name]";"[ItemNumber] = " & [Item Number Field
Name In the Form] )
Click to expand...
I don't know the reason. and it is not important.
But I have one another question. Could you answer this.
If I open a subform I can see the descriptions. O.K.
But When I open the form that the subform link to, descriptions at subform
is not seen. Only "#Name?" writes.

Ofer said:
It called a DLookup in Access.

In the control source of the Iten Desc write
' If item number type is number
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = " & [Item Number Field
Name In the Form] )

' If item number type is text
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = '" & [Item Number Field
Name In the Form] & "'" )

--
I hope that helped
Good luck


AhmtDY said:
I have a database (table). e.g. part no vs part description.
Then I created a form that includes part no, part description, etc.
I don't want to enter the part description whenever I enter the part no. Is
there a method for this. (like a VLOOKUP in excel)
Click to expand...
Click to expand...
 
It should work also as a sub form, you can try this when you use it as a sub
form

=Dlookup("[ItemDesc]";"[Table Name]";"[ItemNumber] = " &
forms![FormName]![SubFormName].Form![Item Number Field])

But it still should work the other way
--
I hope that helped
Good luck


AhmtDY said:
Ofer your expression run, when I changed ";" for ";" ie.
=Dlookup("[ItemDesc]";"[Table Name]";"[ItemNumber] = " & [Item Number Field
Name In the Form] )
Click to expand...
I don't know the reason. and it is not important.
But I have one another question. Could you answer this.
If I open a subform I can see the descriptions. O.K.
But When I open the form that the subform link to, descriptions at subform
is not seen. Only "#Name?" writes.

Ofer said:
It called a DLookup in Access.

In the control source of the Iten Desc write
' If item number type is number
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = " & [Item Number Field
Name In the Form] )

' If item number type is text
=Dlookup("[ItemDesc]","[Table Name]","[ItemNumber] = '" & [Item Number Field
Name In the Form] & "'" )

--
I hope that helped
Good luck


AhmtDY said:
I have a database (table). e.g. part no vs part description.
Then I created a form that includes part no, part description, etc.
I don't want to enter the part description whenever I enter the part no. Is
there a method for this. (like a VLOOKUP in excel)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top