Synchronized subform from button

[jessa_lee]

I put a command button on my main form, and want it to pop open a (sub)form with data that is synched with whatever record is currently displayed on the main form via an unbound text box.

In my software inventory DB, the main form data pulls the Manufacturer's name. From there, I link the SW(software)Title, SWVersion#, CDcount, etc. via individual unbound text boxes (synchronized subform) and a small amount of VBA. The subform I want to pop up now is not built into the form (literal subform), rather opened in a seperate form window by a command button in the main form. The unbound text box is synched with the SW Version#, and I want to pull up the data that's directly linked to that in the Purchasing table. The connection would be through the Version#'s unique ID.

How do I do that?

If you need me to attach any more specifics from my DB, let me know.

-Jessa

 

[Tiro Cupidus]

If I understand correctly, you can put some code in the OnOpen event of your subform that will alter the Record Source based on the SW Version#. When you click the command button, call a function that sets a public variable to the SW Version# value, then in the OnOpen event of the subform, you can refer to that variable.

 

[jessa_lee]

On the command button, the code is:

Private Sub btnPurch_Click()
On Error GoTo Err_btnPurch_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSubSWI_PUR"
    
    stLinkCriteria = "[VER_ID]=" & Me![txtVerN]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnPurch_Click:
    Exit Sub

Err_btnPurch_Click:
    MsgBox Err.Description
    Resume Exit_btnPurch_Click
    
End Sub

But this isn't working. It opens the form, but it shows all the records within the table. Could you help me put together the right code to build on the subform? I only noticed an "Open" or a "Load" option, but I'm still new and unsure about creating an "OnOpen" command.

Thanks,
Jessa

 

[Tiro Cupidus]

I've never used the WHERE clause in the OpenForm method. I was thinking of a different way, but I'll look at both ways when I get home. =)

 

[Tiro Cupidus]

I've attached an example database using what I talked about in my first post. Feel free to poke around it a bit. =) Currently, the only version numbers that will get results (because of data in the tables) are: 1.01, 1.02, and 1.03.

 

[jessa_lee]

Thanks for all your help! I'll be playing with that today. It makes perfect sense - now let's see if I can get it to work!!

-Jessa

 

[jessa_lee]

I'm getting an error when I try to run this:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
   Dim sql As String
         sql = "SELECT VER_ID FROM SWI_VER INNER JOIN SWI_PUR ON "
   sql = sql & "SWI_VER.VER_ID = SWI_PUR.VER_ID WHERE VER_ID = " & varVerN & ";"
   Me.RecordSource = sql
End Sub

The error says Run-time error'3075': Syntax error (missing operator) in query expression 'VER_ID='.

Any ideas on what I'm doing wrong??

 

[Tiro Cupidus]

It seems varVerN doesn't contain a value. Make sure it is declared in a module (in my example database, the module is called 'functions') and when you click on the button to open the subform, make sure you are assigning a value to it.

 

[jessa_lee]

I'm not sure what I'm doing wrong... Could you look at my DB and give me pointers that way? I hope I'm not being too much trouble - you are such a great help!

Open frmSWInventory. The second button on the bottom, "Enter Purchases," is the one I'm working on. The subform I want opened and synched is frmSubSWI_PUR.

Thank you!!!
Jessa

 

[Tiro Cupidus]

What database? =)

 

[jessa_lee]

Hehe - the zip file was too big, so it didn't go through... I PM-ed you - can I send it to you via email?

 

[Tiro Cupidus]

One of the problems I spotted was that you were setting varVerN = txtVerN (like I had it in my database). Your text box is named txtVer. I fixed that, but it's still not working. I think it might have to do with how the value of txtVer is being set (2000), but I can't seem to figure that out right now . I think you might can help me out in that area. I'll keep looking at it when I get home. =)

 

[jessa_lee]

Yes, actually the correct box to link to is the "txtVerN" which is hidden on the form (behind the tabs section). It is identical to the Version#'s unique ID from the table (i.e. txtVerN = SWI_VER.VER_ID). I'm curious to know what you find out.

I've got a few errands to run after work. I'll be checking back in when I make it home tonight

Have I thanked you yet for all your help
Jessa

 

[Tiro Cupidus]

Well, I'm not entirely sure that I changed anything significant, but it sorta works now. I retyped a few things and changed the sql a bit:

Private Sub Form_Open(Cancel As Integer)
   Dim sql As String
         sql = "SELECT SWI_VER.VER_ID FROM SWI_VER INNER JOIN SWI_PUR ON "
   sql = sql & "SWI_VER.VER_ID = SWI_PUR.VER_ID WHERE SWI_VER.VER_ID = " & varVerN & ";"
   Me.RecordSource = sql
End Sub

I had to specify which VER_ID to select and which to put in the where clause. Other than that, I kinda just messed with the hidden text boxes, then put them back to where they were. I'm emailing you my results, but I think I'll download your original again and see if I can come up with something better lol.