Set Focus to Current Row

[NearImpossible]

I have a form with a sub form and that after entering data in the sub form, a calculation query is run to update additional fields, however I am running into issues with Write Conflicts when I try to update the next field.

If I do a Me.Requery then I am able to select the next field and enter data without getting the write conflict, however after the requery, the first field on the first row is selected, which can be a pain if i'm 50 rows in, so I would like for the row where data was entered to remain selected and move to the next field in that row.

After searching the forum, I came across the following code

Dim strBookmark As String
strBookmark = Me.Recordset.Bookmark
Me.Requery
Me.Recordset.Bookmark = strBookmark

which I have adapted as

Private Sub CurrentUsers_AfterUpdate()
     Dim strBookmark As String

     DoCmd.SetWarnings False
     Me.Refresh

     strBookmark = Me.Recordset.Bookmark
          DoCmd.OpenQuery "CurrentTabletCount"
          Me.Requery
     Me.Recordset.Bookmark = strBookmark

     Me.MaxUsers.SetFocus
End Sub

If I update a field on a row where there is already data, it works as intended where it is recalculated and next field in the current row is selected, however if it is the first row of a new table, then I get the following error:

Run-Time error '3021':
No Current record.

Also if I try this on a new row in an existing table, then it will go to the MaxUsers field in the previous row.

any suggestions?

 

[theDBguy]

Hi. It's hard to say what is the best way to handle your situation without seeing it in action, but maybe try handling the error first. In other words, if the code you're using works except for the error messages, then just ignore the error message using an error handler.

 

[isladogs]

From your description I suggest you try using Me.Recalc instead of Me.Requery.
The form will stay on the current record so you can also remove the bookmark code

Also
1. Refresh line shouldn't be needed
2. Remember to switch warnings back on after running the query

Private Sub CurrentUsers_AfterUpdate()

     DoCmd.SetWarnings False
     DoCmd.OpenQuery "CurrentTabletCount"
     Me.Recalc
     DoCmd.SetWarnings True

     Me.MaxUsers.SetFocus
End Sub

 

[NearImpossible]

From your description I suggest you try using Me.Recalc instead of Me.Requery.
The form will stay on the current record so you can also remove the bookmark code

After changing the code to the following

Private Sub CurrentUsers_AfterUpdate()
 
     DoCmd.SetWarnings False
     Me.Refresh

     DoCmd.OpenQuery "CurrentTabletCount"
     Me.Recalc

     Me.MaxUsers.SetFocus
End Sub

I am getting the Write Conflict error whenever I try to enter any data in the MaxUsers field. I click OK and click back in that box and enter the data and all is good until the next record.

Whats kind of odd is if I do this in a database with local tables, it seems to work without issue, however if I am using linked tables, thats when I am getting the write conflict errors

 

[NearImpossible]

Hi. It's hard to say what is the best way to handle your situation without seeing it in action, but maybe try handling the error first. In other words, if the code you're using works except for the error messages, then just ignore the error message using an error handler.

I converted the database to local tables and was going to submit it, however I am not getting the write conflict errors when it is local tables, only linked tables.

 

[isladogs]

Related to the Write Conflicts...Are these linked SQL Server tables?
If so, do you have any bit fields in the table(s) without a default value and some records with null values

 

[NearImpossible]

Related to the Write Conflicts...Are these linked SQL Server tables?
If so, do you have any bit fields in the table(s) without a default value and some records with null values

They are linked SQL tables and there are no bit fields.

As far as the null values, there are only 4 fields to enter the data so the only time a field would be null is because the data hasn't been entered yet.

The fields are as follows

Parent Form
FacilityID - int (PK)
Facility - nvarchar(50)
TabletRatio - int
Notes - nvarchar(max) (Allow Nulls)

SubForm
RowID - int (PK)
FacilityID - int
Name1 - nvarchar(50) (Allow Nulls)
Name2 - nvarchar(50) (Allow Nulls)
CurrentUsers - int (Allow Nulls)
CurrentTablets - int (Allow Nulls)
CurrentBases - int (Allow Nulls)
MaxUsers - int (Allow Nulls)
MaxTablets - int (Allow Nulls)
MaxBases - int (Allow Nulls)

I have 2 queries that run with the first running after entering the Tablet Ratio and Current Users, which calculates the CurrentTablets and CurrentBases and then the second runs after entering the MaxUsers, which calculates the MaxTablets and MaxBases

I've attached a mock database of what i'm trying to accomplish, but like I said, it works without issue with local tables, just write conflicts with linked tables

 

[isladogs]

Write conflicts in linked sql tables can be difficult to pin down.

Check how Access interprets those datatypes. Are they what you would expect?
Suggest you post the sql for both queries.

 

[NearImpossible]

Write conflicts in linked sql tables can be difficult to pin down.

Check how Access interprets those datatypes. Are they what you would expect?
Suggest you post the sql for both queries.

CurrentTabletCount

UPDATE TabletCalculatorFacilities INNER JOIN TabletCalculator ON TabletCalculatorFacilities.FacilityTabletID = TabletCalculator.FacilityTabletID 
SET TabletCalculator.CurrentTablets = RoundUp(Nz([CurrentUsers]/[Tablet Ratio],0)), TabletCalculator.CurrentBases = RoundUp(Nz(([CurrentUsers]/[Tablet Ratio])/5,0))
WHERE (((TabletCalculator.FacilityTabletID)=TabletCalculatorFacilities.FacilityTabletID));

MaxTabletCount

UPDATE TabletCalculatorFacilities INNER JOIN TabletCalculator ON TabletCalculatorFacilities.FacilityTabletID = TabletCalculator.FacilityTabletID 
SET TabletCalculator.MaxTablets = RoundUp(Nz([MaxUsers]/[Tablet Ratio],0)), TabletCalculator.MaxBases = RoundUp(Nz(([MaxUsers]/[Tablet Ratio])/5,0))
WHERE (((TabletCalculator.FacilityTabletID)=[TabletCalculatorFacilities].[FacilityTabletID]));

 

[isladogs]

Ah yes, I remember this now.
The queries look fine so it may be related to datatypes.

Some suggestions you could try.
1. Temporarily remove the RoundUp and Nz. Do you still get a write conflict?
If not restore the Nz and repeat...then the RoundUp.
2. Add a pause before running the second query.
3. Temporarily copy the sql tables to a linked Access dB and relink. Does that work without error?

EDIT Check this first
The WHERE clause in each query looks superfluous as the tables are linked by those fields so the values must be equal. Try removing the WHERE clauses.

 

[NearImpossible]

Ah yes, I remember this now.
The queries look fine so it may be related to datatypes.

Some suggestions you could try.
1. Temporarily remove the RoundUp and Nz. Do you still get a write conflict?
If not restore the Nz and repeat...then the RoundUp.
2. Add a pause before running the second query
3. Temporarily copy the sql tables to a linked Access dB and relink. Does that work without error?

The 2nd query doesn't run until after entering data in the MaxUsers field so not sure where I would put a pause.

I'm about to head out for the day so I will try this and report back tomorrow.


Thanks again for your help

 

[isladogs]

OK good luck.
In case you missed it, do look at the edit added later

 

[NearImpossible]

Ah yes, I remember this now.
The queries look fine so it may be related to datatypes.

Some suggestions you could try.
1. Temporarily remove the RoundUp and Nz. Do you still get a write conflict?
If not restore the Nz and repeat...then the RoundUp.
2. Add a pause before running the second query.
3. Temporarily copy the sql tables to a linked Access dB and relink. Does that work without error?

EDIT Check this first
The WHERE clause in each query looks superfluous as the tables are linked by those fields so the values must be equal. Try removing the WHERE clauses.

1 - Removed the WHERE Clauses and same results
2 - Removed both RoundUp and Nz and get the following when trying to enter data in the next field

The Data has been changed.
Another user edited this record and saved the changes before you attempted to save your changes
Re-edit the record​

3. Temporarily copy the sql tables to a linked Access dB and relink. Does that work without error?

Are you saying to make them local tables and then try to relink them?

 

[isladogs]

No.
I'm trying to identify what is triggering your WriteConflict errors.
You've eliminated a few things for me but I still think its probably a sql datatype issue. Did you ever check the datatypes of those sql tables in Access?

You already know its fine with local tables. So to hopefully pin it down, I'm suggesting testing it using those tables in a linked Access database. I'm fairly sure that will also work.

 

[NearImpossible]

No.
I'm trying to identify what is triggering your WriteConflict errors.
You've eliminated a few things for me but I still think its probably a sql datatype issue. Did you ever check the datatypes of those sql tables in Access?

You already know its fine with local tables. So to hopefully pin it down, I'm suggesting testing it using those tables in a linked Access database. I'm fairly sure that will also work.

Guess i'm not sure what you mean by a linked Access Database.

the tables are as follows
SQL

[B][U]Parent Form[/U][/B]
FacilityID - int (PK)
Facility - nvarchar(50)
TabletRatio - int
Notes - nvarchar(max) (Allow Nulls)

[B][U]SubForm[/U][/B]
RowID - int (PK)
FacilityID - int
Name1 - nvarchar(50) (Allow Nulls)
Name2 - nvarchar(50) (Allow Nulls)
CurrentUsers - int (Allow Nulls)
CurrentTablets - int (Allow Nulls)
CurrentBases - int (Allow Nulls)
MaxUsers - int (Allow Nulls)
MaxTablets - int (Allow Nulls)
MaxBases - int (Allow Nulls)

Access

[B][U]Parent Form[/U][/B]
FacilityID - AutoNumber(PK)
Facility - Short Text
TabletRatio - Number
Notes - Long Text

[B][U]SubForm[/U][/B]
RowID - Auto Number(PK)
FacilityID - Number
Name1 - Short Text
Name2 - Short Text
CurrentUsers - Number
CurrentTablets - Number
CurrentBases - Number
MaxUsers - Number
MaxTablets - Number
MaxBases - Number