Control Box QUERY/MACRO Question

[kealaz]

On my form frmWHEREUSE, I have a combo box with the following information:

Name: PART_NO
Control Source: PART_NO
Row Source Type: Table/Query
Row Source: SELECT tblComboLog.PART_NO, tblComboLog.NAME FROM tblComboLog;


tblComboLog is a table that is created by a macro, mcComboLog

The macro does the following:
Deletes tblComboLog -- qryDeleteCombo
Appends tblDWGLOG to the tblComboLog -- qryCombineA
Appends tblPARTSLOG to the tblComboLog -- qryCombineB

This macro is called On Got Focus for the combo box PART_NO


All of this is working exactly as I want it to, the first time around. The
problem is, when entering information into the form, if I need to enter more
than one record and I enter the first record and tab to continue to the
second record, the combo box PART_NO is not finding the information it needs
from tblComboLog and is empty.

Does the control box NOT get focus when tabbing in from the first record to
the second? Can someone help me understand why this is behaving this way and
what I might do to fix it?

Thank you very much for any and all help!!!

 

[Steve Schapel]

Kealaz,

You probably need to add a Requery action at the end of your macro, in order
to update the row source of the combobox.

If I may add a couple of further comments...
1. "Name" is a Reserved Word (i.e. has a special meaning) in Access, and as
such should not be used as the name of a field or control.
2. This whole process of deleting and appending records to the tblComboLog
table is probably an unnecessarily complicated way of achieving your
purpose. You can probably use a query as the Row Source of the PART_NO
combobox.

--
Steve Schapel, Microsoft Access MVP

On my form frmWHEREUSE, I have a combo box with the following information:

Name: PART_NO
Control Source: PART_NO
Row Source Type: Table/Query
Row Source: SELECT tblComboLog.PART_NO, tblComboLog.NAME FROM tblComboLog;


tblComboLog is a table that is created by a macro, mcComboLog

The macro does the following:
Deletes tblComboLog -- qryDeleteCombo
Appends tblDWGLOG to the tblComboLog -- qryCombineA
Appends tblPARTSLOG to the tblComboLog -- qryCombineB

This macro is called On Got Focus for the combo box PART_NO

All of this is working exactly as I want it to, the first time around.
The
problem is, when entering information into the form, if I need to enter
more
than one record and I enter the first record and tab to continue to the
second record, the combo box PART_NO is not finding the information it
needs
from tblComboLog and is empty.

Does the control box NOT get focus when tabbing in from the first record
to
the second? Can someone help me understand why this is behaving this way
and
what I might do to fix it?

Thank you very much for any and all help!!!

__________ Information from ESET Smart Security, version of virus signature database 4116 (20090529) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[kealaz]

Please help me. How do I do this? I have two tables that I need to combine
the information into one. I thought I had to get all that information into
one table which is why I was using the macro to populate a temp table. I
know now, that I don't need to do this, and that I can do it in a query, but
I don't know how to write the query. Here are the two tables.

tblDWGLOG
PART_NO
DESCRIP
MANUF1
MANUF1_PN
MANUF2
MANUF2_PN
MANUF3
MANUF3_PN

tblPARTSLOG

tblDWGLOG
PART_NO
DESCRIP
MANUF1
MANUF1_PN
MANUF2
MANUF2_PN
MANUF3
MANUF3_PN

And I need to get the information into one source, be it a table or a query.
I tried combining the two tables on a query, and.... the short story is I
don't know what I'm doing. Here is the SQL

SELECT tblDWGLOG.PART_NO, tblDWGLOG.NAME, tblDWGLOG.MANUF1,
tblDWGLOG.MANUF2, tblDWGLOG.MANUF3, tblPARTSLOG.PART_NO, tblPARTSLOG.DESCRIP,
tblPARTSLOG.NAME, tblPARTSLOG.MANUF1, tblPARTSLOG.MANUF1_PN,
tblPARTSLOG.MANUF2, tblPARTSLOG.MANUF2_PN, tblPARTSLOG.MANUF3,
tblPARTSLOG.MANUF3_PN
FROM tblDWGLOG, tblPARTSLOG;

which gives the results side by side. I need it all combined into one result.

Thank you for any and all help!!!

 

[John W. Vinson]

Please help me. How do I do this? I have two tables that I need to combine
the information into one. I thought I had to get all that information into
one table which is why I was using the macro to populate a temp table. I
know now, that I don't need to do this, and that I can do it in a query, but
I don't know how to write the query. Here are the two tables.

tblDWGLOG
PART_NO
DESCRIP
MANUF1
MANUF1_PN
MANUF2
MANUF2_PN
MANUF3
MANUF3_PN

tblPARTSLOG

tblDWGLOG
PART_NO
DESCRIP
MANUF1
MANUF1_PN
MANUF2
MANUF2_PN
MANUF3
MANUF3_PN

And I need to get the information into one source, be it a table or a query.
I tried combining the two tables on a query, and.... the short story is I
don't know what I'm doing. Here is the SQL

You need a UNION query if you want things end to end... but I don't
understand. You have tblDWGLOG twice (nonnormalized) and tblPARTSLOG once,
with no fields stated. I guess there was a copy & paste gone bad? What table
are you going to copy it to? Hopefuly it will have only one MANUF and one
MANUF_PN field...

 

[kealaz]

Yes, you're right, I messed up w/ my copy and paste. Here are the tables. I
am trying to get the information together so that I can call the information
from my combo box from either of them.

tblDWGLOG
PART_NO
DESCRIP
MANUF1
MANUF1_PN
MANUF2
MANUF2_PN
MANUF3
MANUF3_PN

tblPARTSLOG
PART_NO
DESCRIP
MANUF1
MANUF1_PN
MANUF2
MANUF2_PN
MANUF3
MANUF3_PN

I was using a macro to get the information from both tables and appending it
to a third table [tblCOMBOLOG], see OP, but Steve said that I could use a
query to combine the information and then use that query as the row source of
my combo box. I guess that is what I'm trying to do now.

I don't know what a UNION query is, but I will look at that. Thank you very
much John for your help.

 

[kealaz]

John, THANKS so much for your help. I got the UNION query to work and it's
working great with my combo box.

Perfecto!

 

[John W. Vinson]

John, THANKS so much for your help. I got the UNION query to work and it's
working great with my combo box.

Perfecto!

I'd really recommend that you take the next step and create a new, one
manufacturer per row table with an extra field to distinguish Drawings from
Parts (if that is appropriate) and use the union query to populate it.