find a table and populate another in a different form

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

Guest

Hi folks, this is my first time on this site so I hope you can help me out. I
have a table called "TblZQDeviations". I will enter serial numbers in here in
a field called "DeviationSerialNumbers" (I am tracking product issues be
serial numbers of products that I have yet to receive on my system. I need to
know when these serial numbers arrive). I have a form which runs from another
table called "TBLZQInProgressDatabaseSheet". In that form I have 15 part
numbers (for examplr part 1 thru to part15)each of which has a serial number
for traceability. I need to be able to set a warning on this form when an
entered serial number matches one of the entries in "TblZQDeviations".I have
spent many hours at this :(
Maybe there is a better way to trigger such form events??
Rgds
\Paul
 
Nobleman said:
Hi folks, this is my first time on this site so I hope you can help me out. I
have a table called "TblZQDeviations". I will enter serial numbers in here in
a field called "DeviationSerialNumbers" (I am tracking product issues be
serial numbers of products that I have yet to receive on my system. I need to
know when these serial numbers arrive). I have a form which runs from another
table called "TBLZQInProgressDatabaseSheet". In that form I have 15 part
numbers (for examplr part 1 thru to part15)each of which has a serial number
for traceability. I need to be able to set a warning on this form when an
entered serial number matches one of the entries in "TblZQDeviations".I have
spent many hours at this :(
Maybe there is a better way to trigger such form events??
Click to expand...


There are no data driven events. However, the form's
Current event is a handy place to check if the currently
displayed record needs some special handling. If you only
want to display a value differently (e.g. a different
color), then Conditional Formatting (Format menu) is usually
easier.

If your data is in two tables, then it's probably best that
the form use a record source query that joins the two tables
so the deviation info is available with each record. This
will be difficult if your progress table really has part1,
...., part15 fields. Repeating fields like that are a
violation of the rules of Normalization and can make life
rather miserable for you.
 
Thx Marsh, I think I could get somewhere if I could pass the current froms
variable into a variable. I could then open the other tables recordset and
compare the necessary string to this variable. Any ideas on passing a
variable from a form entry..e.g
Forms!FrmDatabaseSheet.Form!ControlPCBSerialNumber = strtest (having
declared strtest as String)
\Paul
 
Ah d**M it I will be sacked soon. No matter what I do I cannot check 15
entries(allbeit one at a time for the moment) against a list of records in
another table to see if there is a match.I have read 2 full books on VBA and
VB over the past while and cannot find the right code to do it. please help.
Maybe I should have started my thread by saying that I had to check 3 entries
in a from against a table listing.....I am only checking on one field so
there must be a way sql/recordset or otherwise to do it aaaaaaaaaaaa
 
I think we need to back up and start from a more detailed
description of yourtable, its fields, the form, what it's
supposed to do.

I also have no idea what you are trying to do with the
recordset nor what the code in your procedure looks like.

Give me the right information to work with and I will try to
save your job ;-))
 
Ok, Marsh, you might want to skip to the second paragraph here to save a lot
of time............................
1)Lets say I have a data entry from where the operator enters data into 3
fields, each of which is a serial number field for a specific component.. eg.
PCB SERIAL NUMBER, CHASSIS SERIAL NUMBER and PUMP SERIAL NUMBER. This data is
put into a table called TblZQDatabaseSheet. The is one other table which
basically consists of a list of serial numbers. This is TblDeviations. It is
called this because quality issues raised through deviations are often
tracked by individual component serial numbers of components.. eg A deviation
might effect a future batch of incoming CHASSIS numbered 100 to 200. Let us
assume we are entering the 3 component serial numbers for a complete unit
through a form based on TblZQDatabaseSheet and the CHASSIS SERIAL NUMBER is
now at 99.(Please bear in mind that these 3 serial numbers have no
relationship to each other and could be put into a unit of number abc45,
basically any number)As the data entry operator is entering the component for
the new unit they will now find that the latest sub CHASSIS SERIAL NUMBER
will be 100 which is impacted by my deviation as mentioned above. I need the
form FrmZQDatabaseSheet to alert the user that they are now entering a sub
assembly serial number which has an associated pdf or note. I hope that is
better explained.

2)Just as an aside to this , currently I have created 3 small subforms which
are attached to the table TblDeviations .If the serial number in CHASSIS
SERIAL NUMBER field is the same as one of those numbers listed in the
TblDeviations table then the form appears to indicate a record which is
actually good enough for me. I have only one field showing in the subform
which is an OLE pdf form which dissapears or reappears automatically if the
two serial numbers match. I thought I had it sorted but then aaaaaaaaaaaaa 2
forms will work automatically as the serial numbers are entered into the
field but one wont until I refresh the form data. I have tried everything
regarding copying fileds renaming destroying relationships and recreating
etc,etc but I cannot get one (actually 2 of 15) fields to update
automatically. If you had any ideas on this then I could forget the whole
query above . To the user, magically appearing red pdf boxes looks very
impressive. I just don't want to any any more refresh code to my from which
has much other code.

Marshall Barton said:
I think we need to back up and start from a more detailed
description of yourtable, its fields, the form, what it's
supposed to do.

I also have no idea what you are trying to do with the
recordset nor what the code in your procedure looks like.

Give me the right information to work with and I will try to
save your job ;-))
--
Marsh
MVP [MS Access]

Ah d**M it I will be sacked soon. No matter what I do I cannot check 15
entries(allbeit one at a time for the moment) against a list of records in
another table to see if there is a match.I have read 2 full books on VBA and
VB over the past while and cannot find the right code to do it. please help.
Maybe I should have started my thread by saying that I had to check 3 entries
in a from against a table listing.....I am only checking on one field so
there must be a way sql/recordset or otherwise to do it aaaaaaaaaaaa
Click to expand...
Click to expand...
 
Nobleman said:
1)Lets say I have a data entry from where the operator enters data into 3
fields, each of which is a serial number field for a specific component.. eg.
PCB SERIAL NUMBER, CHASSIS SERIAL NUMBER and PUMP SERIAL NUMBER. This data is
put into a table called TblZQDatabaseSheet. The is one other table which
basically consists of a list of serial numbers. This is TblDeviations. It is
called this because quality issues raised through deviations are often
tracked by individual component serial numbers of components.. eg A deviation
might effect a future batch of incoming CHASSIS numbered 100 to 200. Let us
assume we are entering the 3 component serial numbers for a complete unit
through a form based on TblZQDatabaseSheet and the CHASSIS SERIAL NUMBER is
now at 99.(Please bear in mind that these 3 serial numbers have no
relationship to each other and could be put into a unit of number abc45,
basically any number)As the data entry operator is entering the component for
the new unit they will now find that the latest sub CHASSIS SERIAL NUMBER
will be 100 which is impacted by my deviation as mentioned above. I need the
form FrmZQDatabaseSheet to alert the user that they are now entering a sub
assembly serial number which has an associated pdf or note. I hope that is
better explained.

2)Just as an aside to this , currently I have created 3 small subforms which
are attached to the table TblDeviations .If the serial number in CHASSIS
SERIAL NUMBER field is the same as one of those numbers listed in the
TblDeviations table then the form appears to indicate a record which is
actually good enough for me. I have only one field showing in the subform
which is an OLE pdf form which dissapears or reappears automatically if the
two serial numbers match. I thought I had it sorted but then aaaaaaaaaaaaa 2
forms will work automatically as the serial numbers are entered into the
field but one wont until I refresh the form data. I have tried everything
regarding copying fileds renaming destroying relationships and recreating
etc,etc but I cannot get one (actually 2 of 15) fields to update
automatically. If you had any ideas on this then I could forget the whole
query above . To the user, magically appearing red pdf boxes looks very
impressive. I just don't want to any any more refresh code to my from which
has much other code.
Click to expand...


It sounds to me like we should be spending our time trying
to figure out why some subforms work and others do not. All
the other stuff seems to be just thrashing around looking
for alternatives instead of addressing the problem directly.

First thing I would do is check each of the subform
control's Link Master/Child properties to see how they
differ.

Another thing to think about is that you keep calling these
serial numbers when your example indicates that they are
text strings, not numbers. I don't know how you are using
these values, but you do have to be aware of the differences
in dealing with the two data types.
 
Back
Top