So here's the situation.

I have a form with two tabs.

Tab One: Search feature through a qry. I change the SQL of the qry for each search.
      Access -> qrySearch which access -> tblWorkgroups, tblRules

Tab Two: Displays the workgroups for reading/adding/editing
    Accesses -> tblWorkgroup, tblRules

Problem: Once I added the search function I was no longer able to add/edit the records under the Workgroup tab. I've read this is because I access the same tables on multiple tabs. Any ideas on how to fix this? (I really don't want to have to do it in two windows :( )

Thanks
hkmulligan

Perhaps if you remove a source from the first tab while working in the second tab, there won't be locking issues.

Add code to the event on the second tab that dumps the recordsource for the first tab when you make the second tab active. Store the source for the first tab in a public variable (SQL String), and then check the public variable for a value when you click on the first tab. If no value, then proceed to generate it normally.

Did that help?

mmcdonal

Sounds like it could work. I'm not too familiar with public variables in Access though. Where would I declare that and how would I call it?

Just create a module and declare the variables there like this:

Public sSQL As String

Then place the text in the string in your event like this:

sSQL = tab1.Rowsource

Or something like that, or when you post the rowsource to tab1, also post it to the public variable:

tab1.Rowsource = "SELECT..."
sSQL = "SELECT..."

Then when you go to tab 2, do this:

tab1.Rowsource = ""

Or if tab 1 is linked to a rowsource statically, then undo that, and put it on with code.

Did that help?


mmcdonal

Got it. I guess it sets that restriction when the form is first loaded, cause I removed the RecordSource from one of the forms and then added to the Form_Load() function to set the recordsource... strange workaround.

I've got one other problem that's probably going to seem a bit n00bish.

If I've got a form that uses a query as a recordsource, how do I use one of the currently selected values in a VB script. (So they have one of the search results selected and I'm trying to switch the second tab to view the selected workgroup)

You mean VBA? Very similar to VBScript, but not quite.

Take the value of a control in a variable, then paste it into a SQL string either in the Where clause of a DoCmd.Open... statemenet, or to set a rowsource, like:

Dim sCustNum As String
Dim sLink As String

sCustNum = Me.CustNum

sLink = "[CustNum] = '" & sCustNum & "'"

DoCmd.OpenForm "frmMyForm", , , sLink

OR:

Public sCustNum As String
Public sSQL As String

sCustNum = Me.CustNum
sSQL = "SELECT * FROM tblCustomer WHERE [CustNum] = '" & sCustNum & "'"

Then:

Me.RowSource = sSQL

Something like that. Or maybe,

Tab2.RowSource = sSQL

Did that help?


mmcdonal