Take a value from a form and pass it to the next

[vapid2323]

My issue is that the form i need to get my value from will be closed before I am able to take the value and pass it onto my new form.

I wanted to use the following code to move the value but with the form closed thats not possible.

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Forms!frmAuditQualification!cboCurrentSite) Then
Me!cboCurrentSite = Forms!frmDefaultStart!cboSiteSelection
End If
End Sub

I am getting the idea that I need to use SQL to do this but I am new to SQL inside VBA.

So in the end what I want to do is take the value from field 'cboSiteSelection
' inside 'frmDefaultStart' and pass it to Field 'cboCurrentSite' in form 'frmAuditQualification'.

Any tips to get me on the right path would be helpfull

 

[vapid2323]

I thought it might be a good idea to mention that the combo box I need to get the value from is unbound

 

[John Big Booty]

What is the reason that you must close the form before passing the value

 

[vapid2323]

What is the reason that you must close the form before passing the value

I thought I would get this question, I am working with a bought UI system and it is coded that way... I wanted to see if I can resolve the issue before contacting them.

I know access has temp tables of some sort... can they be used to hold the value till I can pass it on?

 

[John Big Booty]

Given that you are fiddling round with the code can you not postpone the close event until after the value has been passed to the new form?

 

[vbaInet]

As a suggestion, why not just pass the value in the Unload event of the form? It can be passed to the control in the other form, a global variable or a public variable in the other form.

 

[vapid2323]

Sorry for the late response, I was sick for the last few days.

This is what I found to fix my problem.

If fTableRecordCount("tblQualification", "WHERE fk_SiteID = " & strSQL, True) > 0 Then 'a record exists, just open it
Else 'we need to create a new one
DoCmd.RunSQL "INSERT INTO [tblQualification] (fk_SiteID) SELECT " & Me.cboSiteSelection.Value & " AS EXPR1;"
End If
sChangeSubform 0, "frmAuditQualification:RECORDSOURCE:SELECT * FROM tblQualification WHERE fk_SiteID = " & strSQL, True

 

[vapid2323]

I dont know why its splits my code tags up sorry about that.

 

[vbaInet]

Good to see you have come up with your own way of handling this.

But did you try what I suggested?

 

[boblarson]

I dont know why its splits my code tags up sorry about that.

Somehow code tags got entered twice and just editing didn't pull them out. I just cut the complete text, removed the extraneous code tags and then pasted the message back without them and it fixed it. Just something to note if you ever have that trouble again.

 

[vbaInet]

Somehow code tags got entered twice and just editing didn't pull them out. I just cut the complete text, removed the extraneous code tags and then pasted the message back without them and it fixed it. Just something to note if you ever have that trouble again.

It happens to me sometimes, especially when editing code from a previous post.