Synchronization of database setup and form design

[dylcon]

Thanks Dal. That works very well. That file that was uploaded in post 13 was the wrong one that I uploaded on accident. But, your code still works better than what I had.



Finally, I need to get that PartPOID from the PartPOInfoQuery. Currently, I am using the same method as with the other combo boxes, but it is not working.

Private Sub PONumbercbo_AfterUpdate()    Me.PartPOIDcbo.Requery
    'DoCmd.OpenQuery "PartPOInfoQuery", acViewNormal, acEdit
    
    'Update PartPOIDcbo
    Dim strSource As String
    strSource = "SELECT PartPOInfoQuery.PartPOID " & _
                   "FROM PartPOInfoQuery " & _
                   "WHERE POInfoID = " & Me.PONumbercbo _
                    & " ORDER BY PartPOInfoQuery.PartPOID"
    Debug.Print strSource
 
    Me.PartPOIDcbo.RowSource = strSource
    Me.PartPOIDcbo = vbNullString


End Sub

This way seems like it may not be the best anyways because it will not make it ONLY one PartPOID every single time (if a PONumber exists more than once).
I am assuming this is where the:

    'txtPartPOID = Nz(DLookup([partPOID], "tblpartPO", "PartID = " & txtPartID & " AND POInfoID = " & txtPOInfoID), 0)
        ' then test txtPartPOID for zero to make sure you got a record rather than NULL...

will be used.

I still do not know how this statment is supposed to work, but I will try and figure it out.

Thanks again Dal!!

Just in case here is my current db: AccessForumFolder.zip

[Dal Jeanis]

1) the first parameter of Dlookup is what field will be returned. You want the partPOID

2) the second parameter is what table or query it will come from. Don't know yet.

3) the third parameter is the WHERE condition, but without the word WHERE.

Start here. What is the WHERE you want? you want the partPOID that matches both the Partnumber and POnumber that have been selected. But what are the bound columns of the combo boxes, are they the keys, or the text versions?

Bound column of the Parts combo is 1 - the PartNumber (text format, not Key)
Bound Column of the PO combo is 1 - the PO number (text format, not Key)

Now, it is simpler, for your process, to bind the combo box to the key field. You already have the display width of the columns set so that only the display text is shown to the user. There is also a way to refer to a different column of the control, so even without changing the boundcolumn, you can get to the key with customerNamecbo.Column(0).

So, your WHERE condition will be ...

We now interrupt this post for an important message.

... oh, crap. Well, it's a good "crap".

Looks like I already returned the value you want as Column(0) in the PONumbercbo box. PartPOID, right? It's there already.

In the words of Peewee Herman, "I meant to do that."

[dylcon]

I edited that statement to say:

    PartPOIDcbo = Nz(DLookup([PartPOID], "PartPOInfoQuery", "PartID = " & PartNumbercbo & " AND POInfoID = " & PONumbercbo), 0)

but I am getting a Run Time Error saying that:
"Microsoft Access can't find the field '|1' reffered to in your expression."

I am not quite sure what this means. I have also tried this with the Column Bound being both 0 and 1 for PartPOIDcbo.


Or do you mean that there is no more aditional work that needs to be done? Once the selection is made from the PONumbercbo, the PartPOIDcbo is populated with the corresponding ID? Because if so, I am just getting a blank cbo for this....

[Dal Jeanis]

Private Sub PONumbercbo_AfterUpdate()
    Me.TempPartPOID = Me.PONumbercbo.Column(0)
    Debug.Print Me.TempPartPOID
    
End Sub

Not sure why your DLookup isn't working, but that's the only code you need for putting the PartPOID where you need it. And that control doesn't need to be a combo box; since the user will never see it it should probably be a hidden text box.

[dylcon]

Ok, I understand now. Thanks Dal. Sorry it may seem like you are trying to explain things to a monkey at times

[Dal Jeanis]

No, I feel like I'm trying to explain things with little black squiggles that translate into approximate sounds that have ambiguous meanings, and neither of us has the benefit of vocal tone, facial expression, pointing at objects or pantomime, to help get all the facts and needs across. It's a miracle and a wonder that any technical ideas or complex thoughts can be transferred via this medium at all!

I can tell you that if I hadn't had a copy of your database, it would have taken even more iterations, because I arrived at the solution I found in #17 fortuitously, based upon how I had coded the answer in #15, based upon you had started the code.

My early phrasing of a design is sometimes sloppy, partly because my real world experience tells me that, once I start coding, things will just happen and good and bad accidents will cause the design to change, often for the better. C'est la vie.

[dylcon]

You do an excellent job of conveying exactly what needs to be done and I appreciate that very much so.

On a different note, with the current setup, obtaining the PartPOIDcbo value seems to not be 100% effective as of now. If there is only one PONumber, it works completely fine, but if there are more than one record that have the same PONumber, than the combobox will be populated with all of the different possible PartPOID's that match the PONumber. Is there a different way to go about doing this?

Private Sub PartNumbercbo_AfterUpdate()    Me.PONumbercbo.Enabled = True
    Me.PONumbercbo.Requery
    
    'Update PONumbercbo
    strSourceOne = "SELECT PP.PartPOID, PO.PONumber " & _
                    "FROM tblPartPO AS PP, tblPOInfo AS PO " & _
                    "WHERE PartID = " & Me.PartNumbercbo _
                    & " AND PP.POInfoID = PO.POInfoID " _
                    & " ORDER BY PO.PONumber"
    Debug.Print strSourceOne
 
    Me.PONumbercbo.RowSource = strSourceOne
    Me.PONumbercbo = vbNullString
        
    
End Sub

Because the PONumbercbo is being populated looking for anything that matches the PartNumbercbo AND the POInfoID, there is possibility for more than one options for PartPOIDcbo. It should be matching to PartNumbercbo AND  PONumbercbo​ instead of the POInfoID.


Private Sub PONumbercbo_AfterUpdate()
    Me.TempPartPOID = Me.PONumbercbo.Column(0)
    Debug.Print Me.TempPartPOID
    
End Sub

I need to check if that value is NULL or not...
Right now PartPOIDcbo should only be populated if it finds a record with both the correct PartNumber and PONumber that were selected in the comboboxes, but instead it is populating the cbo with PartPOID's that match only the PONumber... Do you have any suggestions for how to correct this problem? Here is the most recent version of my db in case you need it.



AccessForumFolder.zip

[Dal Jeanis]

The only purpose of a PartPO record is to relate one Part to one PO. For any one Part (X), and any one PO (Y), there should only be one PartPO (XY). A second would have no purpose, and might screw up your reports with erroneous duplicate lines.

Searching for a PartpO with a single Part and a single PO should result in a single PartPO, or NULL if that combination is not yet in the database.

You can enforce this uniqueness with app design/programming, and you can enforce this with a multiple-column unique index on those two fields.

[dylcon]

Dal, I think you may have submitted this before I updated my post....

[Dal Jeanis]

Heh heh. Yup.

[dylcon]

Actually, it still seems to be working fine... I think all I needed was to requery that PartPOIDcbo...

[dylcon]

Dal, for this current setup, can I just delete the PartPOIDcbo altogether? Why I ask is because sometimes a box comes up asking for me to input parameters for that cbo. If you have the TempPartPOID set to the first column and not the PartPOIDcbo. Or are the control source and actual combobox interchangeable within the code?