Dlookup to populate sub form

[Snoopy33]

I appologize if this has been covered in a different post; I searched
but did not find anything that helped with my specific problem.

I have two databases: RMA database and Parts Request database. I have
linked two tables from my RMA database to my Parts Request database.
The two tables that i linked are RMA Generator and Products. RMA
Generator is used on my master form for RMA's and Products is used for
the subform which sometimes contains multiple records (parts) per
single RMA.

My goal is to be able to use DLookup to auto populate as much data as
possible in my Parts Request database to avoid entering information
redundently.

My Parts request database contains two tables; Order Information and
Parts for the same reason as above, the Parts table contains multiple
records for single Part Order.

In using DLookup, I get it to populate the main form with no problems,
but now am getting nowhere in trying to populate the subform. My
questions are:

Is this even possible to populate multiple records based on a field
(not primary key) from the subform's table?

If it is possible, what is the syntax? Here's what i've tried up to
this point

Private Sub Combo107_AfterUpdate()
On Error Resume Next

Combo107.SetFocus
If Combo107.Value > 0 Then
Me.[Customer] = DLookup("customername", "[rma generator]",
"[rma#]=" & Combo107)
Me.[work order number] = DLookup("workorder", "[rma
generator]", "[rma#]=" & Combo107)
Me.[Company] = DLookup("customername", "[rma generator]",
"[rma#]=" & Combo107)
Me.[requested by] = DLookup("[generated by]", "[rma
generator]", "[rma#]=" & Combo107)
Me.[rma#] = DLookup("[categoryid]&[rma#]", "[rma generator]",
"[rma#]=" & Combo107)
Forms.Parts.Qty = DLookup("quantity", "products", "[rmaID]=" &
Me.Combo107)

End If
End Sub

I haven't tried to get the other two fields to populate for the
subform. I didn't figure that there was any reason until i can get one
to populate.

Thanks in advance for any help

 

[strive4peace]

Hi Snoopy (Joe?)

Rather than using dLookup, I like to use combos to show the information.

here is an example using people for the combobox

~~~~~~~~~~~
Combobox Example

Store names in just one place. For instance, if you have a People
table, define a PID (or PeopleID) autonumber field. You will also have
fields for Firstname, Lastname, etc. Then, in other tables, when you
want to identify a person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

~~~~~

from the design view of a form, add a combobox control and give it a
logical name

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname
(you can click in the RowSource property, click on the Builder Button
(...) to the right and use a tool that works like the one you are
probably familar with for queries if SQL is not your strong suit)
--the RowSource can display data from multiple tables as does a query

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.

~~~

btw, your combobox can be hidden! So this becomes a terrific way to
quickly (much faster than dLookup) show information in related tables...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I appologize if this has been covered in a different post; I searched
but did not find anything that helped with my specific problem.

I have two databases: RMA database and Parts Request database. I have
linked two tables from my RMA database to my Parts Request database.
The two tables that i linked are RMA Generator and Products. RMA
Generator is used on my master form for RMA's and Products is used for
the subform which sometimes contains multiple records (parts) per
single RMA.

My goal is to be able to use DLookup to auto populate as much data as
possible in my Parts Request database to avoid entering information
redundently.

My Parts request database contains two tables; Order Information and
Parts for the same reason as above, the Parts table contains multiple
records for single Part Order.

In using DLookup, I get it to populate the main form with no problems,
but now am getting nowhere in trying to populate the subform. My
questions are:

Is this even possible to populate multiple records based on a field
(not primary key) from the subform's table?

If it is possible, what is the syntax? Here's what i've tried up to
this point

Private Sub Combo107_AfterUpdate()
On Error Resume Next

Combo107.SetFocus
If Combo107.Value > 0 Then
Me.[Customer] = DLookup("customername", "[rma generator]",
"[rma#]=" & Combo107)
Me.[work order number] = DLookup("workorder", "[rma
generator]", "[rma#]=" & Combo107)
Me.[Company] = DLookup("customername", "[rma generator]",
"[rma#]=" & Combo107)
Me.[requested by] = DLookup("[generated by]", "[rma
generator]", "[rma#]=" & Combo107)
Me.[rma#] = DLookup("[categoryid]&[rma#]", "[rma generator]",
"[rma#]=" & Combo107)
Forms.Parts.Qty = DLookup("quantity", "products", "[rmaID]=" &
Me.Combo107)

End If
End Sub

I haven't tried to get the other two fields to populate for the
subform. I didn't figure that there was any reason until i can get one
to populate.

Thanks in advance for any help

 

[Snoopy33]

Hi Snoopy (Joe?)

Rather than using dLookup, I like to use combos to show the information.

here is an example using people for the combobox

Thanks for the reply.

I'm not sure how that helps me with my problem though.

I've linked two tables from a different database which have a "one to
many" relationship and want to pull information from those tables to
auto populate a form/subform based on two different tables which have a
"one to many" relationship. The question is how (using combos or
dlookup or whatever) can I pull the information from both of the linked
tables into the tables native to the database with a single control.

The main problem is that not all part orders require an RMA, so some
part orders will be filled in manually, but if an RMA is required, we
don't want to have to enter the information in the RMA database and
turn around and enter the same information in the Part Request
database. if there were only 1 part per rma or part order, I see how
combos would work, but since we will have a single customer, single
address, single reference number, but multiple lines of product i'm
kinda lost on the syntax required to populate the subform.

Thank you again for your help, and thanks in advance for any further
help you can give me.

 

[strive4peace]

Hi Snoopy,

Will you be changin the information? Or just copying it?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Snoopy33]

Thanks again Crystal

I'll just be copying the information in the instances that there is an
RMA involved. Of course, when there's no RMA involved we'll have to
create the part order manually, so I'll have to be able to edit/add the
fields. It works wonderfully in the main form. It populates the
fields and if i need to change anything there's no issue with it. Not
to be redundant, but the sub form is the place that I run into the
problem. It doesn't even grab the first part (record) from the RMA or
even grab the part (record) if there's only a single one associated
with it. I guess that's the part that i'm having the problem with. I
know the syntax is correct for the main form, but something is wrong
with either the syntax, my approach or maybe just my expectationsIs.
Is it even possible to populate multiple records based on the output of
a single control?

I'm pretty new at this. I hope that I'm explaining the problem in
understandable terms.

 

[Snoopy33]

Ok, i'm reading what i posted above and would like to make clear one
portion of it. When i said i want it to auto pupulate multiple records
in the sub form, I should have said that i want it to CREATE multiple
records in the sub form based on the matching records in the linked
tables.

I can't say thank you enough for your help

 

[strive4peace]

Hi Snoopy,

I am reluctant to tell you how to do this as you really should be
linking to data that is already in the database -- unless you need to
make changes to it... that said, here is what you need to know:

first, change the NAME property of Combo107 to something meaningful
Combo107 --> RMA_num
(not a good idea to use special characters such as # in fieldnames or
spaces)

~~~
In the design view of the [rma generator] table, remove the 0 for
DefaultValue from RMA_Num

if you will look at the design view that the table is based on, the
DefaultValue of all numbers is set to 0 (zero) -- this is done by
Access. Unless you change it, that is what will happen if another value
is not filled out to replace it (or it is deleted) ... OR... you REMOVE
the DefaultValue (which is what I usually do for foreign keys so
referential integrity can be enforced).
~~~

back to your form... make the RowSource of the RMA_num combo include the
columns you wish to copy

ie:

SELECT RMA_Num
, customername
, workorder
, [generated by]
, categoryid
, quantity
FROM [rma generator]
ORDER BY customername, RMA_Num

since Column Indexing starts at 0, not 1, the second column of data will
be RMA_Num.column(1)

'~~~~~~~~~
if not isnull(me.RMA_num) then
'add record to subform
with me.subform_controlname.form
.recordset.addnew
' ----- Customer
if len(trim(nz(me.RMA_Num.column(1),""))) > 0 then
.[Customer] = Me.RMA_Num.column(1)
.[Company] = Me.RMA_Num.column(1)
end if
' ----- if workorder is text
if len(trim(nz(me.RMA_Num.column(2),""))) > 0 then
.[workorder] = Me.RMA_Num.column(2)
end if
' ----- if workorder is a number
if len(trim(nz(me.RMA_Num.column(2),0))) <> 0 then
.[workorder] = Me.RMA_Num.column(2)
end if
' ----- generated by
if len(trim(nz(me.RMA_Num.column(3),""))) > 0 then
.[generated by] = Me.RMA_Num.column(3)
end if
'etc
end with
end if
'~~~~~~~~~~~

btw, it appears that you are changing what the field named RMA# contains
from the main table to the related table... IMHO, fieldnames should be
the same when the data is the same -- and the fieldname should be
different if the data is different

~~~~~~~

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Snoopy33]

Thank you,
I'll give it a try monday when i return to work.

btw, these are two well established databases witch have been in place
for near a year. I only recently have been charged with trying to
eliminate some keystrokes that our "two finger typers" have to make
when ordering parts and issuing RMA's.

The parts database contains a field that merely references the RMA
number "if" there is one associated with the part request; some parts
are being bought outright and don't require a return authorization for
an old part. That field is named the same on the parts form as the
primary key field on the RMA Generator Table from the linked database.
I'm not sure if that's what you're referring to, but i'm not attempting
to change any record, just trying to create a record based on already
existing information in another database. It does get a bit confusing
in the sense that there are indeed field names that are the same that
doesn't necessairly contain the same information and fields that
contain the same data from both databases that have different names.
When I created these databases I had no intention at that time to try
to pull data from one to the other.

I may at some point try to create a single database that does the work
of both (or maybe more) of our existing databases and attempt to
migrate all existing data into the new one. I will definately at that
time try to not make the same mistakes that I've made on these (but
make new one's i'm sure), but for now that's a bit more ambitious than
i care to be.

Be looking for my email for your Word Doc.

your kindness is greatly appreciated

 

[strive4peace]

Hi Ben,

Thank you

"well established databases"

that doesn't necessarily make them right! I have done a lot of camping
-- duct-tape and bailing wire are good friends ... but not permanent
solutions.

for example... I had the opportunity to do classroom training for
several years on Microsoft products (not just Access, but Excel, Word,
PowerPoint, Windows ...) Many times, those who signed up for the
Intermediate and Advanced classes came back to take the Beginners course
-- just because you have done it for a long time doesn't mean you did it
right or know the basics

A strong foundation is everything when it comes to Access. Structuring
data is the most important thing to do well -- as you learn and your
knowledge grows, it is important to translate that to data structure
changes -- just as you would repair a crack in the foundation of a
building the moment it is discovered.

I have sent my Access Basics document to you -- if you did not receive
it, please let me know

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Snoopy33]

OK, I'm sorry to have upset you with my response. That was not my
intention.

It appeared that you were telling me that my field names that
contained the same data should have been named the same.



"btw, it appears that you are changing what the field named RMA#
contains
from the main table to the related table... IMHO, fieldnames should
be
the same when the data is the same -- and the fieldname should be
different if the data is different "



I was merely stating that the databases were not intended to share
information at the time they were built (about a year ago), therefore
I had no need nor did I have the foresight to make the field names the
same. That was my only point; I'm not building these as we speak.
They have been in place (well established) for some time. Right or
wrong, what's done is done, and without hours of undoing and redoing
(time that I don't have) I'm have to leave them as they are.

My approach may in fact be comparable to "duct tape and bailing wire"
and I appreciate your observation. For now I'll be content to try
your suggestion, and if it doesn't work, we'll be happy with the
functionality that we currently have.


Thank you for your help
Snoopy33

 

[strive4peace]

Name, Time, Foundation, Rose
---

Hi Ben,

In my first response back to you, the combobox example, important
techniques were missed because the fields were just displayed and not
stored -- because the end was not what was imagined, the steps were
overlooked.

when something like

"first, change the NAME property of Combo107 to something meaningful"

is said,

.... it is because what you call something is so important -- in code,
the Name property will be referred to and it is best if it makes sense
-- especially when you come back a year later and need to modify the logic.

I realize that time is a luxury -- and not one we always have,,, but
please don't blame me for recognizing the cracks in the foundation. We
will help you, Ben

~~~

"the databases were not intended to share information at the time they
were built"

I can appreciate that -- especially if they came from Excel
spreadsheets... where so much information starts.

Names are important. Identifying something and always calling it that
-- no matter where you are -- just makes things easier. When Data is
converted from Excel, often times what is in the column heading should
really be a field Description in Access (what is used to populate the
StatusBarText automatically) and the fieldname itself, IMHO, should not
contain spaces or special characters except underscores, and should be
concise yet descriptive. On forms, controlnames bound to a field
(ControlSource), it is convenient to make the control Name the same as
the ControlSource.

If a control is created on a form that is not bound, the control name
shoul also indicate what it is capturing or displaying. For instance:
Filter_RMA_num, Display_Workorder, Calc_Fullname ...

Notice when I gave you code, I did not have to use square brackets
around 'Me.RMA_Num' --> that is because special characters were not used
and Access did not need the beginning and ending 'markers' to interpret
what it said. It is best to use letters, numbers, and underscores in
names -- and always start with a letter.

# is used in Access to delimit dates as quotes are used to delimit strings

#1/1/07#

... a lesson for the future anyway

~~~

"nor did I have the foresight to make the field names the same"

One things about Access... the first step is the most important. Excel
is forgiving ... add another column and formulas are automatically
adjusted. Access, however, is a different story -- like a house, really
-- you are always building on top of what you have already laid ... and
what is most important?

the foundation

and what is that? In Access, it is data structure -- not just physical
grouping into tables, data types, and sizes -- but also in names -- just
makes things easier for the future.

If a 'rose' is in another garden, it is still a 'rose'?

~~~

I was not upset -- sorry I gave that impression,,, just trying to make a
point -- social graces are not my strong suit -- I try to give you the
information that you truly need as I see it, but I guess my bluntness is
sometimes misinterpreted.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Snoopy33]

First let me say that you have the patience of a saint to deal with
newbies like me.

In looking through my databases (all of them unfortunately) in
treaking, tuning, adding functions and bastardizing them i've created
a mess from an administrative viewpoint. You are absolutely correct.
Your foundation has to be right or what you put on top is only going
to further convalute it. I've got way too many "comboNNN" and
"textNNN" controls on my forms to get a good idea of where to start in
implementing a change. I will definately take a valuable lesson from
this and I greatly appreciate you for pointing this out to me; I'm
just sorry that I got offended by it instead of looking at what you
were telling me objectively.

Second, I've not ignored your suggestion, but have not had the time to
try to implement it as of yet. I'll definately let you know how that
goes.

I'm seriously considering starting from scratch and trying to create a
master database that encompasses all of our needs and attempt to merge
or recreate data where needed. I currently have 10 databases in use
within several departments. It will be a long painful process, but I
believe that we'll be more flexible in changes as well as having
stronger data integrity between departments.

Thank you for your help! I'm sure I'll be needing it again in the
near future

Snoopy33
Ben

 

[strive4peace]

You're welcome, Ben

"I'm just sorry that I got offended by it instead of looking at what you
were telling me objectively."

don't worry, probably a common reaction when you have just learned that
you need to redo your work ... at least you're on the right track now
.... THINKING about your data is actually more important than starting
your rebuild -- the more time you put in now to identify what you have
(actually visualize your data, it helps when structuring it)

Building from scratch is a good idea -- and, if you have much data, it
can be converted to the new structures when they are done.

Expect to spend a couple weeks to a month just planning your structure
-- set it up, print relationship diagram (remember to lay it out so it
flows smoothly -- VERY important!), make changes, print diagram, make
changes -- it will be an iterative process.

Don't go to the other extreme, though, and normalize too much ... for
example, I find it easier to store duplicate phone numbers than to
create a table with phone numbers and a cross-reference table between
people and phones -- so I just put the PID (PersonID) in the table where
I store phone numbers.

Address, though, it is definitely better to store the address and have a
cross-ref table between people (company or human) and addresses ...
Addresses are linked to Cities (may choose to store CityID) and States
(I just store the 2-char code instead of an ID since it is short, but
use States table to lookup -- only works for American addresses, of course)

you might ask why use cross-reference table for addresses and not phones
.... InputMask -- Phone is a field I use it on and it makes things harder
to do the "right" way So I give in to a bit of redundant storage for
ease of programming.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*