trying to minimize Write Conflicts in a multi-user database
in [General]
|
Prev: não acho meu orkut
Next: maike yordano pirela vera
From: Jack Leach dymondjack at hot mail dot on 14 Jan 2010 20:15 I'm not sure about question 3 (I haven't done this before, but I will certainly keep it in mind, it seems an excellent idea). For question 1), you wouldn't be using the boilerplate DoCmd.OpenForm method and criteria... you presumably already have the form open, you just need to give it a recordsource... Private Sub Me.cboRecords AfterUpdate() Me.Recordsource = "SELECT * FROM table WHERE [ID] = " & Me.cboRecords Me.Requery End Sub Ideally, this would be a saved query name that pulls the value direct from the combo on the form... performance would be better like that, I believe. As for Q2, it depends what you want your first record to be... but what I would do is move the above code into its own procedure rather than the afterupdate, then you can call the procedure from both the Open event and the combo AfterUpdate event: Private Sub psGoToRec(lRecID As Long) Me.Rowsource = "SELECT * FROM Table WHERE [ID] = " & lRecID Me.Requery End If Private Sub cboRecord_AfterUpdate() psGoToRec(lRecID) End Sub Private Sub Form_Open(Cancel As Integer) psGoToRec(<your default opening id here>) End Sub I would be curious to see a reply for the navigation (and new records) portion of the question... no lights are shining in my head on that at the moment... -- Jack Leach www.tristatemachine.com "I haven''t failed, I''ve found ten thousand ways that don''t work." -Thomas Edison (1847-1931) "Paul" wrote: > I'm working on implementing John Spencer's description of a parent form that > has only one record open at a time, and three questions occur to me: > > 1. Do you use DoCmd.OpenForm stDocName, , , stLinkCriteria in the > AfterUpdate event of a combo box to move between records? > > 2. You would be using a SQL SELECT statement to populate the combo box used > to navigate between records once the form is open, but how do you determine > which record to open when you initially load the form? That is, how do you > extract the value of the first record (or for that matter, any record) in > that SQL statement to use as the stLinkCriteria when you first open the > form? > > 3. Since the main form has only one record loaded at any time, the normal > navigation buttons won't be able to do anything. Are there any Web sites > that address building custom navigation buttons for single-record forms? > > Thanks in advance, > > Paul > > > . >
From: Paul on 14 Jan 2010 23:22 Thanks for the help with this, Jack. The code you suggested: Me.Recordsource = "SELECT * FROM table WHERE [ID] = " & Me.cboRecords works great in the combo box's AfterUpdate event because it retrieves the desired record instantly, and leaves only the single record in the form, which is exactly what I want. However, I'm having trouble getting it to work in the form's Load event because the combo box is null, and the assignment statement above results in an error since Me.cboRecords is null. Similarly, if I try to put the value of the combo box in the criteria field of the form's query, the form is blank because the value in the combo box is null. Is there any way I can force the combo box to retrieve one of its own records (the first one would be ok) as soon as the form loads, so it can provide a non-null value for the ID in the assignment statement and the criteria of the form's query? I've tried to Requery the combo box before setting the RecordSource of the form, but that didn't seem make a difference. Any suggestions how I can overcome problem of the empty combo box when the form loads? Paul
From: Tom Wickerath AOS168b AT comcast DOT on 15 Jan 2010 02:23 Hi Paul, Try setting the Recordsource for the form to this SQL statement (or to a saved query with this SQL statement): SELECT * FROM table WHERE 1=0 This query is guaranteed to return zero records. Remove the code that you had in the Form_Load event, which was apparently running the query that grabbed criteria from the combo box. To address your original question about minimizing Write Conflict errors, Are there any tables that include memo, hyperlink, OLE Object, or the new multi-value field (Access 2007 .accdb only)? The reason I ask is that these fields can cause page locking to be invoked, when you might otherwise assume that record locking is being used. For JET databases, I have gotten into the habit of breaking memo fields out to a separate table, with a 1:1 relationship, instead of including the memo field in the same table with other fields. This way, when a user clicks into the memo field on a form, they have immediately committed any changes to the parent record and vice-versa, clicking out of the memo field on the form to any other field commits changes to the memo data. I base this on the following quote from Microsoft: "Also, record-level locking is not enabled for Memo data types." Source: http://support.microsoft.com/kb/275561, under the title: "Record-level locking". Another reason for moving memo fields to their own table is so that I never run into this situation: http://support.microsoft.com/kb/296389 Keep in mind that hyperlink and OLE Object fields involve the same pointer mechanism that memo fields do, so the above discussion applies to these data types equally well. The other thing that I've been doing in all my released applications for the past couple of years is running code at startup, via an Autoexec macro, that uses ADO to establish record level locking for the first person to open the BE database. Subsequent users will connect to the BE database with the same locking that the initial user establishes: http://support.microsoft.com/?id=306435 Michael Kaplan points out on his blog site that when a user selects the option to use Record Level locking, that this is only a request, not a demand. So, by using the ADO code in the above article, you are essentially demanding record level locking. This KB article clearly states this for the new MVF data type in Access 2007 causes page locking: http://support.microsoft.com/kb/918578 Hope this helps some. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Paul" wrote: > Thanks for the help with this, Jack. The code you suggested: > > Me.Recordsource = "SELECT * FROM table WHERE [ID] = " & Me.cboRecords > > works great in the combo box's AfterUpdate event because it retrieves the > desired record instantly, and leaves only the single record in the form, > which is exactly what I want. > > However, I'm having trouble getting it to work in the form's Load event > because the combo box is null, and the assignment statement above results in > an error since Me.cboRecords is null. Similarly, if I try to put the value > of the combo box in the criteria field of the form's query, the form is > blank because the value in the combo box is null. Is there any way I can > force the combo box to retrieve one of its own records (the first one would > be ok) as soon as the form loads, so it can provide a non-null value for the > ID in the assignment statement and the criteria of the form's query? > > I've tried to Requery the combo box before setting the RecordSource of the > form, but that didn't seem make a difference. > > Any suggestions how I can overcome problem of the empty combo box when the > form loads? > > Paul
From: John Spencer on 15 Jan 2010 08:12 As for question 3 3. Since the main form has only one record loaded at any time, the normal navigation buttons won't be able to do anything. Are there any Web sites that address building custom navigation buttons for single-record forms? You can build code to move next or move previous based on the combobox you are using to get the record. You just need to know which record you are on and where that is in the combobox. Then get the value of the previous/next row in the combobox, set the combobox to that row and execute the code to select the record based on that value. If you need help building that code, post back. If I have some free time later today, I will try to help. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County
From: Jack Leach dymondjack at hot mail dot on 15 Jan 2010 10:08
For a combo... The .ListIndex property (0 based) tells what item you have selected (or -1 for no selection). Use this and the .Column(.ListIndex) to get the the currently selected record. The .ListCount (1 based) tells how many list items there are in the source of the combo. Use this to determine if the current selection is the last one. If .ListIndex = .ListCount - 1 Then LastRecord = True You should be able to "select" a record using the Column property: Me.Combo = Me.Combo.Column(Me.Combo.ListIndex + 1) This should give you an idea where to start. I haven't tried yet, but am still a bit curious about how to handle a recordsource of the main form for a new record... I *think* that, per Tom's suggestion, you can set the SQL to WHERE 1 = 0 (which will display no records), and be able to enter a new record this way. I think that's the case, but haven't tested yet. This really is a great idea, I will also begin to do some redesigning (haven't had an issue with write conflicts as of yet, but that's not to say I won't...) And many thanks to Tom for making the points on Memo, OLE and MVF fields... I was not aware of this either. -- Jack Leach www.tristatemachine.com "I haven''t failed, I''ve found ten thousand ways that don''t work." -Thomas Edison (1847-1931) "John Spencer" wrote: > As for question 3 > > 3. Since the main form has only one record loaded at any time, the normal > navigation buttons won't be able to do anything. Are there any Web sites > that address building custom navigation buttons for single-record forms? > > You can build code to move next or move previous based on the combobox you are > using to get the record. You just need to know which record you are on and > where that is in the combobox. Then get the value of the previous/next row in > the combobox, set the combobox to that row and execute the code to select the > record based on that value. > > If you need help building that code, post back. If I have some free time > later today, I will try to help. > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > . > |