Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 9th, 2007, 03:55 AM
Registered User
 
Join Date: Aug 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Tabs + Subforms + Add/Edit = No Go

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
 
Old August 9th, 2007, 06:49 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old August 9th, 2007, 07:46 AM
Registered User
 
Join Date: Aug 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
 
Old August 9th, 2007, 07:50 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old August 9th, 2007, 09:21 PM
Registered User
 
Join Date: Aug 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)

 
Old August 10th, 2007, 06:54 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Add/Edit Flow question jwiedmier BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 5 October 26th, 2007 06:35 PM
Edit Forms with Master/Multiple SubForms natwong Access 1 February 27th, 2007 01:01 PM
how to know who edit or add records? drachx General .NET 1 October 13th, 2004 05:36 AM
how to know who edit add records? drachx SQL Server 2000 9 October 13th, 2004 04:28 AM
Given the datalist. How do I add edit mode? macupryk General .NET 0 October 8th, 2004 12:29 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.