vba to change what the User sees in a form combobox

[JahJr]

I've got a user form with the following
cboNewOrExist = combobox for selecting New Relationship or Existing Relationship
txtNewRel = text box for entering New relationship name.
cboExistRel = Combobox for selecting Existing Relationships

txtNewRel is only visible if cboNeworExist =New Relationship
cboExistRel is only visible if cboNeworExist = Existing Relationship

If cboNewOrExist = New Relationship and the AddAccount button is pressed I would like the following to happen
1. add the new relationship to the tblRelationships
2. ReQuery qryRelationship
3. hide txtNewRel text box
4. show cboExistRel combo box
5. cboNewOrExist = Existing Relationship
6. cboExistRel should be populated with the value just added to tblRelationships from txtNewRel. This value is in the qryRelationship with the highest RelationshipID number.

I've got 1-5 solved but I can't manage to change what the user sees in the cboExistRel

If Me!cboNewOrExist.Column(0) = "New Relationship" And Me!txtNewRel.Value <> vbNullString Then
        strSQLRelationship = "INSERT INTO tblRelationships(Relationship) VALUES (Me!txtNewRel.Value);"
        DoCmd.RunSQL strSQLRelationship
        DoCmd.Requery (qryRelationship)
        Me!txtNewRel.Visible = False
        Me!cboExistRel.Visible = True
        MaxRelationship = DMax("[tblRelationships.RelationshipID]", "qryRelationship")
        Me!cboNewOrExist.Value = Me!cboNewOrExist.Column(0, 1)
        Me!cboExistRel.Value = ????
    End If

I've tried the following with no success

Me!cboExistRel.Value = txtNewRel.Value
Me!cboExistRel.Value = (DLookup("[Relationship]", "qryRelationship", "[tblRelationships.RelationshipID] =" & MaxRelationship))

 

[theDBguy]

Hi. Have you tried something like?

Me.cboExistRel.Requery

 

[arnelgp]

requery it first, then assign the value.

 

[JahJr]

I did a little digging and I think i partialy figured it out. cboExistRel is getting its values from qryRelationship.
If I use cboExistRel.value = 90 it shows the 90th entry in the qryRelationship. The dmax doesn't help because the query is alphabetical and is trimmed depending on who owns the relationship.

With that solved I need to figure out a way to reference the qryRelationship by their Row Number. I'm guessing the easiest way todo this is add a field to qryRelationship that is a running count.

I cant figure this one out.

 

[arnelgp]

you can, it's not that hard.
after you save the new relations, I think its in txtNewRel textbox?

requery your cboExistRel:

me.cboExistRel.Requery

then, lookup the RelationshipID of txtNewRel:

me.cboExistRel = Dlookup("[RelationshipID]", ""qryRelationship", "[Relationship]='" & Me.txtNewRel & "'")

 

[JahJr]

The problem that i see is that cboExistRel.Value is the Record Number from qryRelationship. The Record Number and the RelationshipID do not correspond. Is there a way to lookup the Record Number from a query.

 

[arnelgp]

you just substitute "Record Number" to "RelationshipID" from the dlookup.
the criterial is the txtNewRel, which is already saved.

 

[JahJr]

That does not seem to work. "Record Number" is not a defined column in qryRelationship. If you run qryRelationship and select one of the records the number that shows at the bottom is what I'm referring to as Record Number.
I tried this

DLookup("Record Number", "qryRelationship", "[Relationship]='" & Me.txtNewRel & "'")

When i run that i get error 3075 syntax error (missing operator) in query expression 'Record Number'.

 

[JahJr]

I found a work around that seems to be working. I'm sure there is a better way.

me.cboExistRel.value = DCount("[tblRelationships.Relationship]", "qryRelationship", "[tblRelationships.RelationshipID]<=" & _
DLookup("[RelationshipID]", "qryRelationship", "[Relationship]='" & Me.txtNewRel & "'")) - 1

 

[The_Doc_Man]

Here is a simple bit of advice specific to this question:

Access won't tell you anything you didn't tell it first.

It applies here because as you have pointed out, the thing you want to query isn't a member of the recordset 'cause it's not in the underlying table. This is an issue in design that, basically, you built the suitcase but forgot to attach the handle. (Metaphorically speaking, of course.) So you have no way to pick up what you wanted.

When i run that i get error 3075 syntax error (missing operator) in query expression 'Record Number'.

I can 'splain that one for you... You have a space in a field name, but to Access SQL, a space is an element separator. You could make it work like this:

DLookup("[COLOR="Red"][[/COLOR]Record Number[COLOR="red"]][/COLOR]", "qryRelationship", "[Relationship]='" & Me.txtNewRel & "'")

This is also why you really don't want spaces as part of field names.

 

[JahJr]

Thank you for your response. I spent some time laying this out before i got started. I had hoped i did not have any design problems but I guess that is not the case.

From what your saying I believe I need to add a RepNumber field to the tblRelationships. I had originally not done this because the RepNumber would have been stored in 2 places, tblAccountNumbers and tblRelationships.

I've attached a sample of what I'm working on. Please disregard the forms as they are a work in progress. I would like to know your thoughts on the design.

 

[The_Doc_Man]

From what your saying I believe I need to add a RepNumber field to the tblRelationships. I had originally not done this because the RepNumber would have been stored in 2 places, tblAccountNumbers and tblRelationships.

In a properly designed relational database, this type of thing not only CAN happen, it MUST happen. But the issue is how they are used.

Your words make it clear that you see RepNumber as though it will be a permanent number to identify one record vs. another. This could be accomplished by making a RepNumber field which is an AutoNumber prime key (PK). This numeric field (it is a type LONG integer) becomes the permanent, i.e. lifetime identifier of one and only one record in the table where it is the PK. It must be invariant forever, unlike other non-prime key fields (technically, indexed fields) that theoretically could be edited to change the contents.

Where it appears in the table where it is defined as an Autonumber, you can use that PK for one purpose and ONLY one purpose - uniqueness. You cannot depend on uniformity of numbering because of some actions that cause it to "waste" a number on aborted records. But you can have up to a few BILLION unique record IDs.

In any other table where you need to associate a record to a specific record via that unique number, you make a LONG (integer) field that becomes the Foreign Key (FK). An FK "points" to the PK in the appropriate table. By copying the PK of a "parent" table to the FK of a "child" table (you can use independent and dependent nomenclature if you prefer), you establish that the two records are related.

This is the basis for the word "Relation" in "Relational Database." You might need to do some reading on this subject. The good news is that if you then use the Database Tools ribbon to bring up the Relationships panel, you can define formalized relationships that Access wizards can see AND USE!!! Suddenly you would find that the query builder knows exactly what to do to build multi-table JOIN queries so that you can exploit the known and properly defined relationship.

You MIGHT need to do some reading on some topics:

Database Normalization - you should understand 1st, 2nd, and 3rd normal forms at least.
Relationships - the difference between 1-to-1, 1-to-many, and many-to-1 relations.
Relational Integrity - a way to assure that relationships remain self-consistent.
Synthetic vs. Natural Keys - a way to decide what would be a good key for your tables.

You should be able to search this forum for those topics. You can also find stuff on the web in general, but in a general web search, I might stick to some .EDU sites at first.

If you have already studied these, then OK - but the brief peek I took at your DB didn't really tell me anything on that subject. I know you had some PK declarations in your tables, but you probably want to avoid text PKs unless they are very short strings and good candidates for a natural PK.

It is not my style to go into someone else's DB and tweak because I don't know enough about your exact environment. I could easily do more harm than good by direct diddling. You will have to decide for yourself how your data needs to be conjoined. But what I described above is mechanically how you would do it.

 

[arnelgp]

why is your form unbound?
also the combos are unbound?

here is what I got so far. your temp account table is not in your demo so I did not test if it will save.

you must press the save button on new relationship.