Populating one text field from another (1 Viewer)

[Researcher]

I have two tbls, one called Inventory and the other is called description.

Inventory stores all the users input data, the fields are:
ID, Part Number, Description, Date, Technician, Notes, Organization, Qty Add, Qty Subtract and Requirement.

Description is a tbl where I store the fields:
ID, part number and description.

What I am trying to do when data is input to the part number field I would like the description field to be populated with the description that fits that part number.

I have used on the description field control source:
=DLookUp("[Description]","[Description]","[Part_Number]=" & [Part_Number])

and

=DLookup("[Description]","[Description]","[ID]=" & Forms!Inventory!ID)

Neither with no success...can anyone help me out with my syntax?

BTW...Part_Number and Description are text fields in both tbl's. and the description tbl was originally used to import the data into a combo box which I changed to a text box.

 

[Ranman256]

the parameters are: DLookUp("field","table/query",sWhere)

if Part# is a string ,you must use quotes:=DLookUp("[Description]","tPartNums","[Part_Number]='" & [Part_Number] & "'")

 

[Researcher]

Could you be more explicit with your description, I am not sure how you are applying "String"...as in a specific data entry in the field? "tPartNums", is that a specific part number I am looking for? I want the field description to be populated with the description that fits the part number I enter and if the part number does not exist I want to be able to create it along with it's description...forgive my ignorance I am new to using DLookup on control source, I have done pretty well with many procedures including VBA but have built my knowledge as needed over the years. The form name I am using is called "Inventory".

Thanks Ranman256

 

[Minty]

You have a normalization issue here. You have a table listing all your part numbers and descriptions with a unique ID(tblDescrriptions). Your Inventory table appears to be storing the same information Part Number, and description along with the actual movement data.

You should simply be storing the PartNumberID in your Inventory table as the other information can and should only be stored in the Part Number list.

I would also recommend Only having a single Qty field in your Inventory table, that holds negative numbers for stock out and positive numbers for stock in. Your stock balance is then a simple sum of the one field.

 

[Researcher]

Having only one qty field, how can I calculate what is added to inventory and what is subtracted? Currently I am calculating through the query.

 

[Minty]

You would record it as a transaction - you indicate what is in or out simply by putting in a positive or negative number - in simplest terms;

UsageID	PartID 	Transaction Type	Qty	Date
1	1	Initial Stock		 25	1/1/2017      [COLOR="Green"]'Movement In[/COLOR]
2	2	Initial Stock		 64	1/1/2017      [COLOR="Green"]'Movement In[/COLOR]
3	1	Sale			 -3	3/1/2017      [COLOR="Red"]'Movement Out[/COLOR]
4	1	Sale			 -5	5/1/2017      [COLOR="Red"]'Movement Out[/COLOR]
5	2	Sale			-12	6/1/2017      [COLOR="Red"]'Movement Out[/COLOR]
6	1	Customer Return 	  3	12/1/2017     [COLOR="Green"]'Movement In[/COLOR]

Now your stock balance for any given item is a query on this one table grouped by PartID with a sum of the qty field.

Obviously you could and maybe should also have a table listing your transaction types, and possibly within that a field indicating if it is a In or Out / Positive or Negative transaction type.

You could them add a 3rd type which would be a stock check, which you could use to act as a "Only add things up from the last stock check transaction" type flag.

 

[Researcher]

I get that on the qty field issue, unfortunately I am still hung up on the dlookup, trying to enter a part number on a record add and have the next field "description" automatically show me the nomenclature of the part number.
It would be best for me to keep it simple at this stage, I just need something functional.

 

[Minty]

Are you entering the part number from a combo box from your parts table? If you are (and it's probably the best way unless you have thousands of part numbers) you can avoid the dlookup completely.

Simply bring the description into combo box as a third column and you can use that value to update the displayed (not stored) description field on your form.

 

[Researcher]

Actually I am scanning the part number into the part number field and then trying to populate the description field with a description of the part number whether it is scanned or entered manually.

The part number field is a text box and the description field was originally a combo box where I have displayed the part number and description in the drop down, but when selected in the drop down shows me the part number in the description field.

I was told not possible to populate a combo box from a text field so I changed the combo box to a text box and that is where I am currently.

BTW, your help is appreciated Minty, Thank You

 

[Minty]

You can scan the part number straight to the combo box, I'm actually working on a simple shipping form that does exactly that ...

You just need to set the combo box column widths to 0;5 to hide the first column. If you want to upload a stripped down version of your database I'm sure we could fix that form for you very quickly.

 

[Researcher]

Ok, this is a really scaled down version of my database due to who I am writing it for, so it looks really bad, but the un-scaled version looks pretty good except for the DLookup and Qty field issues.

Thanks for taking a look...I'm sure you know how to bypass the security level login...

 

[Minty]

I've made a couple of changes to your inventory form and one change to the underlying table. Have a look and see what I've done.

I've not broached the stock qty issue yet as that will require a pretty fundamental change of thought processes for you. But one that will make your life significantly easier in the long run.

Note how I'm not storing the description in the inventory table and I'm only storing the PartID from the values table.

Also you have a field called Date in some of your tables. Change this to something else OrderDate, EntryDate etc, for two reasons. Firstly Date is a reserved word and using it a s field name will cause you grief sooner rather than later. Secondly calling it something more meaningful will save you having to remember which Date field you are trying to deal with.

Finally please remove all the spaces from your field names. They are also a pain to deal with long term. I would have a read up here https://access-programmers.co.uk/forums/showthread.php?t=225837 for some excellent guidance.

 

[Researcher]

Re: (Solved) Populating one text field from another

Thanks Minty for your help, once I made the changes everything fell into place, to re-assemble the field types from number to actual text for part number, Organization and Technician i used a query and built relationships that pulled it all together. I like the fact that I can scan into a combo field, I read somewhere on here that it was not possible, but it was.

Thanks again for your help..