Combo Box Painted Corner for Dirk, Pavel or any other Guru

[Guest]

Sorry guys, I had to start a new thread because I made a mess of my other one. It seems I've painted myself into a corner and I hope you can provide a way out! I use "*" to indicate a primary key. I know this is an awful lot to digest - any help would be greatly appreciated

I have two combo boxes that I need to correct now that I know more clearly what I need them to do

1. cbProfileI
This is bound to tblProfileID.txtProfileID* and needs to
- Permit and record new entries
- Go to a Profile ID upon selection

2. cbProfilesAssociation
This is in a subform. Its Row Source is: SELECT tblProfiles.txtProfileID, tblProfiles.Description, tblProfiles.Type FROM tblProfiles
This needs to
- Make selections according to the Row Source
- Dbl Click and open a form that filters for the selected txtProfileID*

Previously, I had cbProfilesAssociations worked out with Dirk. He helped me hard code the form names into the Dbl Click Event Procedure. This worked great! However, Pavel suggested a LookUp for the forms in case the form names change. I prefer this because I know I'll be adding and changing forms quite often

I've added the form names in the FormToOpen field in tblProfileTypes which consists of
txtProfileType
Descriptio

Again, any help would be appreciated - thanks!

 

[Dirk Goldgar]

Please see comments and questions interspersed ...

Sorry guys, I had to start a new thread because I made a mess of my
other one. It seems I've painted myself into a corner and I hope you
can provide a way out! I use "*" to indicate a primary key. I know
this is an awful lot to digest - any help would be greatly
appreciated.

I have two combo boxes that I need to correct now that I know more
clearly what I need them to do.

1. cbProfileID
This is bound to tblProfileID.txtProfileID* and needs to:
- Permit and record new entries.
- Go to a Profile ID upon selection.

When you say "bound to tblProfileID.txtProfileID", do you mean that the
*form* is bound to tblProfileID and cbProfileID has txtProfileID as its
ControlSource? Or -- what seems more likely to me -- do you just mean
that the combo box's RowSource is a query that selects the field
txtProfileID from tblProfileID?

When you say "permit and record new entries", do you mean that the combo
should allow the creation of new records in the table tblProfileID? Or
are you talking about creating new records in the table or query to
which the form is bound?

I think you'd better post the following information:

1. the Record Source of the form, including the SQL if it's a query, and
the field list if it's a table

2. the Row Source of the combo box "cbProfileID", including the SQL if
it's a query, and the field list if it's a table

3. the Control Source of the combo box "cbProfileID"

2. cbProfilesAssociations
This is in a subform. Its Row Source is: SELECT
tblProfiles.txtProfileID, tblProfiles.Description, tblProfiles.Type
FROM tblProfiles;
This needs to:
- Make selections according to the Row Source.
- Dbl Click and open a form that filters for the selected
txtProfileID*.

Let's include in your post the following additional items:

4. the name of the subform control (on the main form) that displays this
subform

5. the Record Source of the subform's source object; that is, of the
form object that is displayed in the subform control. Please include
the SQL if it's a query, and the field list if it's a table.

6. the values of the Link Master Fields and Link Child Fields properties
of the subform control

7. The Control Source of the combo box, "cbProfilesAssociations"

Previously, I had cbProfilesAssociations worked out with Dirk. He
helped me hard code the form names into the Dbl Click Event
Procedure. This worked great! However, Pavel suggested a LookUp for
the forms in case the form names change. I prefer this because I know
I'll be adding and changing forms quite often.

I've added the form names in the FormToOpen field in tblProfileTypes
which consists of:
txtProfileType*
Description

So the table tblProfileTypes actually has three fields: txtProfileType,
Description, and FormToOpen?

Again, any help would be appreciated - thanks!

In addition to the profile type, what other filter criteria are to be
applied on opening the desired form? Is the form supposed to be opened
to show only the record(s) related to the current record on the main
form or the subform? If so, what is the name of the key field on each
form, that is to be used in filtering the records?

 

[Dirk Goldgar]

John -

Look carefully amid the quotes for my followup questions and comments,
delimited by *******.

Thanks, Dirk. Ugh - I just finished a response to this and then had a
computer glitch! I'll try it again. Please see below.

----- Dirk Goldgar wrote: -----

Please see comments and questions interspersed ...


of my > other one. It seems I've painted myself into a corner
and I hope you > can provide a way out! I use "*" to indicate a
primary key. I know > this is an awful lot to digest - any help
would be greatly > appreciated.

When you say "bound to tblProfileID.txtProfileID", do you mean
that the *form* is bound to tblProfileID and cbProfileID has
txtProfileID as its ControlSource? Or -- what seems more likely
to me -- do you just mean that the combo box's RowSource is a
query that selects the field txtProfileID from tblProfileID?

JL
Correction - tblProfileID is actually tblProfiles.
frmProfiles is bound to tblProfiles and cbProfileID has txtProfileID
as its ControlSource.

When you say "permit and record new entries", do you mean that
the combo should allow the creation of new records in the table
tblProfileID? Or are you talking about creating new records in
the table or query to which the form is bound?

JL
It should allow the creation of new records in tblProfiles.

I think you'd better post the following information:

1. the Record Source of the form, including the SQL if it's a
query, and the field list if it's a table

JL
SELECT tblProfiles.txtProfileID, tblProfiles.Version,
tblProfiles.Type, tblProfiles.Description, tblProfiles.SKUSCCITF,
tblProfiles.UPCUCCEAN, tblProfiles.OriginDate,
tblProfiles.ApprovedDate, tblProfiles.ActiveDate,
tblProfiles.InactiveDate, tblProfiles.RevisedDate,
tblProfiles.RevisionHistory, tblProfiles.AllergenComments FROM
tblProfiles;

tblProfiles is the foundation of my database. I have ONE profile and
MANY types (CG, CN, BG, etc.).
frmProfiles is the foundation of my forms. I use it as a template or
"header" for the many types and their unique forms.

2. the Row Source of the combo box "cbProfileID", including the
SQL if it's a query, and the field list if it's a table

JL
tblProfiles.txtProfileID

*******
That looks like a Control Source, not a Row Source. Are you saying that
the Row Source of this combo box is tblProfiles, or a query that returns
the txtProfileID field from tblProfiles? What is actually in the Row
Source property?
*******

3. the Control Source of the combo box "cbProfileID"
txtProfileID

*******
Are you telling me this combo box is bound to the same table that serves
as its Row Source? But you want to use this combo box both for
navigation -- to go to an existing Profile if one is selected -- and to
enter a new Profile if an entry is made that is not in the combo's list?
Please confirm. This is tricky, since you have to use code to
distinguish the two cases and avoid changing the Profile for an existing
record.
*******

tblProfiles.Type > FROM tblProfiles;

*******
I'm not sure I follow here. If this is in a subform that is linked to
the main form by the txtProfileID field, according to the Link
Master/Child Fields properties, then you'd never need to enter anything
in this field, nor would you need to display it, since it would be
displayed on the main form. There's still something I don't understand
about your setup.
*******

Let's include in your post the following additional items:

4. the name of the subform control (on the main form) that
displays this subform

JL
I'm lost on this one. I don't see this property in the main form's
property box...?

*******
If you have the main form open in design view, then click the properties
button to view that form's properties, then click just once on the
subform, the property sheet should now show the properties for the
subform control. The Name property is on the Other tab of the property
sheet, as well as beeing displayed in the caption bar of the property
sheet.
*******

5. the Record Source of the subform's source object; that is,
of the form object that is displayed in the subform control.
Please include the SQL if it's a query, and the field list if
it's a table.

JL
Sorry - I'm lost on this one, too. The subform is linked to
tblProfilesAssociations which has only two text fields that are a
combination primary key:
ProfilesAssociations
txtProfileID

*******
So the subform is bound directly to tblProfilesAssociations, and that
table has two fields named "ProfilesAssociations" and "txtProfileID"?
*******

6. the values of the Link Master Fields and Link Child Fields
properties of the subform control

JL
Link Master Fields: txtProfileID
Link Child Fields: txtProfileID

7. The Control Source of the combo box, "cbProfilesAssociations"

JL
ProfilesAssociations

*******
I don't understand. From the row source you've given for
cbProfilesAssociations, you're going to be storing a txtProfileID value
in both of the fields in tblProfilesAssociations. What is the purpose
of this subform, and of table tblProfilesAssociations? Is it to
establish a relationship between one profile ID and another?
*******

He > helped me hard code the form names into the Dbl Click Event
LookUp for > the forms in case the form names change. I prefer
this because I know > I'll be adding and changing forms quite
often. >> I've added the form names in the FormToOpen field in
tblProfileTypes > which consists of:

So the table tblProfileTypes actually has three fields:
txtProfileType, Description, and FormToOpen?

Yes.


In addition to the profile type, what other filter criteria are
to be applied on opening the desired form? Is the form supposed
to be opened to show only the record(s) related to the current
record on the main form or the subform? If so, what is the name
of the key field on each form, that is to be used in filtering
the records?

I think this is where I messed things up before. The double click
needs to open the form for the selected txtProfileID. Each
txtProfileID is assigned a Type (CG, CN, BG, etc.). Each type has its
own form which is listed in tblProfileTypes.FormToOpen.

Whew! That's a lot - thanks for walking me through this!

*******
I'm really confused, John, and I need to understand what these tables
and forms are supposed to represent before I'll feel comfortable
advising you. I know this is getting long-winded, but would you mind
explaining what this is all about?

 

[Guest]

My responses below in between your *******

----- Dirk Goldgar wrote: ----

John

Look carefully amid the quotes for my followup questions and comments
delimited by *******

Thanks, Dirk. Ugh - I just finished a response to this and then had
computer glitch! I'll try it again. Please see below
of my > other one. It seems I've painted myself into a corne
and I hope you > can provide a way out! I use "*" to indicate
primary key. I know > this is an awful lot to digest - any hel
would be greatly > appreciated
that the *form* is bound to tblProfileID and cbProfileID ha
txtProfileID as its ControlSource? Or -- what seems more likel
to me -- do you just mean that the combo box's RowSource is
query that selects the field txtProfileID from tblProfileID
Correction - tblProfileID is actually tblProfiles
frmProfiles is bound to tblProfiles and cbProfileID has txtProfileI
as its ControlSource
the combo should allow the creation of new records in the tabl
tblProfileID? Or are you talking about creating new records i
the table or query to which the form is bound
It should allow the creation of new records in tblProfiles
query, and the field list if it's a tabl
SELECT tblProfiles.txtProfileID, tblProfiles.Version
tblProfiles.Type, tblProfiles.Description, tblProfiles.SKUSCCITF
tblProfiles.UPCUCCEAN, tblProfiles.OriginDate
tblProfiles.ApprovedDate, tblProfiles.ActiveDate
tblProfiles.InactiveDate, tblProfiles.RevisedDate
tblProfiles.RevisionHistory, tblProfiles.AllergenComments FRO
tblProfiles
MANY types (CG, CN, BG, etc.)
frmProfiles is the foundation of my forms. I use it as a template o
"header" for the many types and their unique forms
SQL if it's a query, and the field list if it's a tabl
tblProfiles.txtProfileI

******
That looks like a Control Source, not a Row Source. Are you saying tha
the Row Source of this combo box is tblProfiles, or a query that return
the txtProfileID field from tblProfiles? What is actually in the Ro
Source property

J
Sorry - I typed them together. The Row Source is tblProfiles

******

3. the Control Source of the combo box "cbProfileID
txtProfileI

******
Are you telling me this combo box is bound to the same table that serve
as its Row Source? But you want to use this combo box both fo
navigation -- to go to an existing Profile if one is selected -- and t
enter a new Profile if an entry is made that is not in the combo's list
Please confirm. This is tricky, since you have to use code to
distinguish the two cases and avoid changing the Profile for an existing
record.

JL
Yes - clearly I'm insane

*******

tblProfiles.Type > FROM tblProfiles;

*******
I'm not sure I follow here. If this is in a subform that is linked to
the main form by the txtProfileID field, according to the Link
Master/Child Fields properties, then you'd never need to enter anything
in this field, nor would you need to display it, since it would be
displayed on the main form. There's still something I don't understand
about your setup.

JL
This is a junction table setup. I'm storing associated profiles in tblProfilesAssociations. This is what you helped me through before. I haven't changed anything except to add a FormToOpen field in tblProfileTypes.

*******

I'm lost on this one. I don't see this property in the main form's
property box...?

*******
If you have the main form open in design view, then click the properties
button to view that form's properties, then click just once on the
subform, the property sheet should now show the properties for the
subform control. The Name property is on the Other tab of the property
sheet, as well as beeing displayed in the caption bar of the property
sheet.

JL
DOH! I feel stupid - I knew this but just don't have as strong an Access vocabulary.

The answer is: sfrmProfilesAssociations

*******

5. the Record Source of the subform's source object; that is,
of the form object that is displayed in the subform control.
Please include the SQL if it's a query, and the field list if
it's a table.
Sorry - I'm lost on this one, too. The subform is linked to
tblProfilesAssociations which has only two text fields that are a
combination primary key:
ProfilesAssociations
txtProfileID

*******
So the subform is bound directly to tblProfilesAssociations, and that
table has two fields named "ProfilesAssociations" and "txtProfileID"?

JL
Yes - and they are both primary keys.

*******

6. the values of the Link Master Fields and Link Child Fields
properties of the subform control
Link Master Fields: txtProfileID
Link Child Fields: txtProfileID
ProfilesAssociations

*******
I don't understand. From the row source you've given for
cbProfilesAssociations, you're going to be storing a txtProfileID value
in both of the fields in tblProfilesAssociations. What is the purpose
of this subform, and of table tblProfilesAssociations? Is it to
establish a relationship between one profile ID and another?

JL
Exactly!

*******

He > helped me hard code the form names into the Dbl Click Event
LookUp for > the forms in case the form names change. I prefer
this because I know > I'll be adding and changing forms quite
often. >> I've added the form names in the FormToOpen field in
tblProfileTypes > which consists of:
txtProfileType, Description, and FormToOpen?
to be applied on opening the desired form? Is the form supposed
to be opened to show only the record(s) related to the current
record on the main form or the subform? If so, what is the name
of the key field on each form, that is to be used in filtering
the records?
needs to open the form for the selected txtProfileID. Each
txtProfileID is assigned a Type (CG, CN, BG, etc.). Each type has its
own form which is listed in tblProfileTypes.FormToOpen.

*******
I'm really confused, John, and I need to understand what these tables
and forms are supposed to represent before I'll feel comfortable
advising you. I know this is getting long-winded, but would you mind
explaining what this is all about?

JL
Absolutely. I'm putting together a database that relates Packaging, Finished Goods, Suppliers, Facilities, Raw Materials, Formulas, etc. Would it help if I sent you what I have so far?

 

[Dirk Goldgar]

My responses below in between your *******.

----- Dirk Goldgar wrote: ----- [snip]

*******
Are you telling me this combo box is bound to the same table
that serves as its Row Source? But you want to use this combo
box both for navigation -- to go to an existing Profile if one
is selected -- and to enter a new Profile if an entry is made
that is not in the combo's list? Please confirm. This is
tricky, since you have to use code to distinguish the two cases
and avoid changing the Profile for an existing record.

JL
Yes - clearly I'm insane

It's not impossible, just tricky, as I said. Have you gotten this part
to work, or do you need help with the coding? If so, under what
specific circumstances do you want to use the combo box to create a new
Profile record? Does the form have to be on a new record already, or
would you allow the user to enter an unrecognized profile ID while on an
existing record, and let that action move the form to the new record and
begin filling it out?

[snip]

*******
I'm really confused, John, and I need to understand what these
tables and forms are supposed to represent before I'll feel
comfortable advising you. I know this is getting long-winded,
but would you mind explaining what this is all about?

JL
Absolutely. I'm putting together a database that relates Packaging,
Finished Goods, Suppliers, Facilities, Raw Materials, Formulas, etc.
Would it help if I sent you what I have so far?

Only if you can cut it down to the point where in compacted, zipped form
it's less than 1MB in size. If you can do that, you may send it to me
at the address you get when you remove NO SPAM from the reply-address of
this message.

 

[Guest]

Thanks to Dirk's diligance and help - this has been resolved! Here are the solutions

I'm VERY happy to have this together now! Thank you so much for the support

Very good. You're welcome

Just to make sure I understand: now I can add/subtract/rename form names in my FormToOpen field without having to adjust the code

Yes, that's how it should work

We had been looking at web-based databases and my first question to sales reps was: Why can't we just do this in Access? Their responses were that Access simply can't manage this amount of data nor can it be structured to a high level of functionality. Well, I didn't believe them and I'm finding that Access has more power than I ever imagined. It's very satisfying to put something together and have it function how you like it

I don't know how much data you're talking about, or other requirements of the database. Data volume could be an issue, naturally, if you start to get up around 1.5GB for the database size -- 2GB is the absolute limit for a single .mdb file, but you begin to run into problems before you get there. The number of simultaneous users can also be a limiting factor

The other cbox is in frmProfiles. This form I'm going to delete. In it's place will be frmProfilesTemplate. I use frmProfilesTemplate to simply view the profile info of each ID. I also use it as a template with which to construct other forms. I'm sure you noticed this

I don't think I've been very clear with this. Let me try in other words. cbProfileID needs to
-Allow new entries (Limit To List = "No")
-Open up the entire txtProfileID record upon selection
-Open the form associated with the txtProfileID upon double-click

I hope that's more clear

I think so. On form "frmProfilesTemplate" at the moment, the txtProfileID field is represented by a text box, not a combo box, so I did a little work on frmProfiles instead. I changed the combo box cbProfileID to have properties (RowSource, ColumnCount, ColumnWidths) similar to those for cbProfilesAsssociations on sfrmProfilesAssociations. Then I gave it these event procedures for the AfterUpdate and DblClick events

'------ start of code -----
Private Sub cbProfileID_AfterUpdate(

Dim strNewProfile As Strin

With Me.cbProfileI

' Capture the profile ID the user has selected or entered
strNewProfile = .Value & vbNullStrin

If Len(strNewProfile) = 0 The
' We've no idea what the user has in mind, s
' leave this alone
Exit Su
End I

' Is the user's entry an existing profile ID
If .ListIndex = -1 The
' This is not an existing profile ID
' If we aren't on a new record, undo this entry
' go to a new record, and re-enter it there
If Not Me.NewRecord The
.Und
Me.Und
RunCommand acCmdRecordsGoToNe
.Value = strNewProfil
' Me.cbProfileID = strNewProfil
End I
Els
' This is an existing profile ID
' Undo the entry on this record an
' go to the record that was entered
.Und
Me.Und
Me.Recordset.FindFirst "txtProfileID='" & strNewProfile & "'
End I

End Wit

End Su

Private Sub cbProfileID_DblClick(Cancel As Integer

Dim strFormToOpen As Strin

With Me!cbProfileI
' If no profile has been selected, we can't do anything useful
If IsNull(.Value) The
Exit Su
End I

' Here a profile has been selected, so open the appropriate for
' for the selected profile

' Lookup the form, returning a null string if none is specified
strFormToOpen = vbNullString &
DLookup("FormToOpen", "tblProfileTypes",
"txtProfileType='" & .Column(2) & "'"

If Len(strFormToOpen) = 0 The
' Whoops, no form for this profile type
MsgBox "Sorry, the form for this profile type hasn't been specified.",
vbExclamation, "Can't Open Form"
Else
DoCmd.OpenForm strFormToOpen, acNormal, _
WhereCondition:="txtProfileID='" & .Value & "'"
End If

End With

End Sub
'------ end of code ------

The DblClick code should look very familiar. The AfterUpdate code seems to work as I think you are asking, though you're going to run into problems if the user changes other fields on the form, and then changes cbProfileID. In such a case, all the user's changes will be lost. It would be possible to work around that, but I wasn't sure what you would want to do in such a case.