ACCESS FEED: Februari 2021

Minggu, 28 Februari 2021

[MSAccessProfessionals] HIDE BY DEFAULT MSG

Hi,
I need to remove by default msg of my below code. Is this possible?

strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID

Thanks in advance.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

Thanks Duane Hookom,

I just simply changed to below and it worked will with me.

DoCmd.RunSQL "DELETE * FROM T_EmpOTFooter where Verified = 0 And OTID = Forms!F_EmpOTHeader!OTID"

Thanks for your support.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

The dbSeeChanges is not part of the SQL statement.

strSql = "DELETE * FROM T_EmpOTFooter WHERE OTID= " & Forms!F_EmpOTHeader!OTID & " And Verified = 0", dbSeeChanges

You can copy from the debug window and paste/modify it in a new blank query SQL view to view the results:

SELECT * FROM T_EmpOTFooter WHERE OTID= 3 And Verified = 0

Regards,

Duane

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of SAYYED ASHFAQUE HUSSAIN via groups.io <ashfaque_online=yahoo.com@groups.io>
Sent: Sunday, February 28, 2021 12:24 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

Thanks Duane Hookom,

The debug print is giving following result:

DELETE * FROM T_EmpOTFooter WHERE OTID= 3 And Verified = 0

It means he is not picking up Verified field data. For this main record # 3, there are 5 footer records out of which 2 are unchecked so I need them to delete from footer tbl and keep rest 3 saved.

And when it is reaching to execute below:
CurrentDb.Execute strSql, dbFailOnError
It produces Run-time error 3622
"You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an Identity Column.

When I tried with below:
strSql = "DELETE * FROM T_EmpOTFooter WHERE OTID= " & Forms!F_EmpOTHeader!OTID & " And Verified = 0 dbSeeChanges"

Syntax error - Missing Operator

Please advise...

Sabtu, 27 Februari 2021

Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

Yes, Option Explit placed.

On Sunday, February 28, 2021, 09:24:33 AM GMT+3, SAYYED ASHFAQUE HUSSAIN via groups.io <ashfaque_online=yahoo.com@groups.io> wrote:

Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

Please include the previous reply in your post so we understand what was stated before.

The Debug.Print strSQL is there for a reason. Did you open the debug window (press Ctrl+G) to see the result?

I don't believe you ever answered the question about the data type of OTiD. The code assumes it's numeric. Is the code being run in the form F_EmpOTHeader?

strSql = "DELETE * FROM T_EmpOTFooter WHERE OTID= " & Forms!F_EmpOTHeader!OTID & " And Verified = 0"

Debug.Print strSql

CurrentDb.Execute strSql, dbFailOnError

Regards,

Duane

From: SAYYED ASHFAQUE HUSSAIN via groups.io <ashfaque_online=yahoo.com@groups.io>

Thanks Paul,

I have renamed my footer tbl field from CheckRightOT to "Verified " and then tried with following

strSql = "DELETE * FROM T_EmpOTFooter WHERE OTID= " & Forms!F_EmpOTHeader!OTID & " And " & Verified = 0

Debug.Print strSql

CurrentDb.Execute strSql, dbFailOnError

It says Verified variable not defined. Whereas Verified is checkmark of reach record in footer table

Please help. The only Idea is to delete all those records from footer tbl (form) which are not checked.

Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

Thanks Paul,

I have renamed my footer tbl field from CheckRightOT to "Verified " and then tried with following

strSql = "DELETE * FROM T_EmpOTFooter WHERE OTID= " & Forms!F_EmpOTHeader!OTID & " And " & Verified = 0

Debug.Print strSql

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Kamis, 25 Februari 2021

Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

Good catch Paul!

From: Paul Baldy <Pbaldy@gmail.com>

Also, you're not executing the SQL. This:

CurrentDb.Execute dbFailOnError

Should be:

CurrentDb.Execute strSQL, dbFailOnError

Paul

Sent via mobile device

-------- Original message --------

From: Duane Hookom <duanehookom@hotmail.com>

You have extra quotes on either side of the AND. Do you have Option Explicit in your module header? Does your code compile?

Is the OTID data type text or numeric? This code should work with numeric.

strSQL = "DELETE * FROM T_EmpOTFooter WHERE OTID = " & Forms!F_EmpOTHeader!OTID & " And CheckRightOT = 0; "

I would also add a line

debug.print strSQL

This will print the value of strSQL in the immediate/debug window (press Ctrl+G to view).

Regards,

Duane

From: SAYYED ASHFAQUE HUSSAIN via groups.io <ashfaque_online=yahoo.com@groups.io>

Hi,

My BE is on SQL Server whereas FE in off course in Access. I have a header form and a sub-form added into. This is you can say one-to-many record register. It is register to record dept wize overtime of the employee.

I have below code thru which I want to delete those record which have UNCHECKED (Bit type field on server).

Private Sub OMApproval_Click()  Dim Msg As String, Style As Integer, Title As String, Response As Integer  Dim strSQL As String  Msg = "You are about to delete all unchecked entries from sub-form."  Style = vbOKCancel + vbQuestion + vbDefaultButton2  Title = "Continue?"  Response = MsgBox(Msg, Style, Title)  If Response = vbOK Then  strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID  CurrentDb.Execute dbFailOnError  Me!SF_EmpOTFooter.Form.Requery  Else  MsgBox "No record deleted", vbOKOnly, "Info"  End If  End Sub

But the problem is it always producing above error.
With some of people suggestion I changed to StrSQL line as follows but still same error.
Please note that OMApproval is checkmark on my header form.
strSQL = "DELETE * FROM T_EmpOTFooter WHERE OTID = " & Forms!F_EmpOTHeader!OTID & " And CheckRightOT = 0; "

Still same....please advise.
Thank you in Advance...

Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

Also, you're not executing the SQL. This:

CurrentDb.Execute dbFailOnError

Should be:

CurrentDb.Execute strSQL, dbFailOnError

Paul

Sent via mobile device

-------- Original message --------

From: Duane Hookom <duanehookom@hotmail.com>

Date: 2/25/21 6:43 AM (GMT-08:00)

To: MSAccessProfessionals@groups.io

Subject: Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

You have extra quotes on either side of the AND. Do you have Option Explicit in your module header? Does your code compile?

Is the OTID data type text or numeric? This code should work with numeric.

strSQL = "DELETE * FROM T_EmpOTFooter WHERE OTID = " & Forms!F_EmpOTHeader!OTID & " And CheckRightOT = 0; "

I would also add a line

debug.print strSQL

This will print the value of strSQL in the immediate/debug window (press Ctrl+G to view).

Regards,

Duane

From: SAYYED ASHFAQUE HUSSAIN via groups.io <ashfaque_online=yahoo.com@groups.io>

Private Sub OMApproval_Click() Dim Msg As String, Style As Integer, Title As String, Response As Integer Dim strSQL As String Msg = "You are about to delete all unchecked entries from sub-form." Style = vbOKCancel + vbQuestion + vbDefaultButton2 Title = "Continue?" Response = MsgBox(Msg, Style, Title) If Response = vbOK Then strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID CurrentDb.Execute dbFailOnError Me!SF_EmpOTFooter.Form.Requery Else MsgBox "No record deleted", vbOKOnly, "Info" End If End Sub

Re: [MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

You have extra quotes on either side of the AND. Do you have Option Explicit in your module header? Does your code compile?

Is the OTID data type text or numeric? This code should work with numeric.

strSQL = "DELETE * FROM T_EmpOTFooter WHERE OTID = " & Forms!F_EmpOTHeader!OTID & " And CheckRightOT = 0; "

I would also add a line

debug.print strSQL

This will print the value of strSQL in the immediate/debug window (press Ctrl+G to view).

Regards,

Duane

From: SAYYED ASHFAQUE HUSSAIN via groups.io <ashfaque_online=yahoo.com@groups.io>

Private Sub OMApproval_Click()  Dim Msg As String, Style As Integer, Title As String, Response As Integer  Dim strSQL As String  Msg = "You are about to delete all unchecked entries from sub-form."  Style = vbOKCancel + vbQuestion + vbDefaultButton2  Title = "Continue?"  Response = MsgBox(Msg, Style, Title)  If Response = vbOK Then  strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID  CurrentDb.Execute dbFailOnError  Me!SF_EmpOTFooter.Form.Requery  Else  MsgBox "No record deleted", vbOKOnly, "Info"  End If  End Sub

_._,_._,_

[MSAccessProfessionals] Type Mismatch Run-time error ...Tired of this issue...

Private Sub OMApproval_Click()  Dim Msg As String, Style As Integer, Title As String, Response As Integer  Dim strSQL As String  Msg = "You are about to delete all unchecked entries from sub-form."  Style = vbOKCancel + vbQuestion + vbDefaultButton2  Title = "Continue?"  Response = MsgBox(Msg, Style, Title)  If Response = vbOK Then  strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID  CurrentDb.Execute dbFailOnError  Me!SF_EmpOTFooter.Form.Requery  Else  MsgBox "No record deleted", vbOKOnly, "Info"  End If  End Sub

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Selasa, 23 Februari 2021

Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Jim,

Thank you for your kind words. I'm so glad to have a group like this to give me ideas even though I'm not a professional.

Dave W

On 23/02/2021 17:35, Jim Wagner wrote:

Dave,

I am so glad you have it working. I know the feeling of relief when you find a solution.

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Tuesday, February 23, 2021 10:32 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Jim,

Thanks. I had to do a bit more to stop Text15 ending up with a flashing cursor. My code now looks like:

Private Sub List11_AfterUpdate()
Me.Painting = False
Text13.Requery
Text15.SetFocus
DoCmd.FindRecord Text13, acEntire, False, acSearchAll, True, acCurrent, True
List11.SetFocus
Me.Painting = True
End Sub

I was a bit concerned that as both text boxes are still enabled, they could be selected by the cursor, but strangely this is not the case.

Dave W

On 23/02/2021 12:51, Jim Wagner wrote:

Dave

If you do not need to see the controls. Make the background transparent and the font color the same color as the form. That way it is there but not visible to the eye

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Tuesday, February 23, 2021 4:17 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Paul,
Thanks for that - it could be useful, but I've found a solution now. I added another text box (called Text15) to the form, with its data source as the ID field of the table to which the form is bound. My code for List11 is now:

Text13.Requery
Text15.SetFocus
DoCmd.FindRecord Text13, acEntire, False, acSearchAll, True, acCurrent, True

The only trouble is that I can't make Text13 invisible because then it can't be found, and I can't make Text15 invisible because then it can't take focus.
Text13 data source is =DLookUp("[1st photo ID]","[FirstPhotoOfYear]","[Year]=[List11]")

Dave

On 23/02/2021 01:34, Paul Baldy wrote:

The code below can easily be adapted to work on the same form instead of opening another. You can also try the combo box wizard and choose the third option, "Find a record...". You'll only see that option if the form is bound to a table though, if memory serves. The wizard will use a bookmark as well, or at least it used to.

http://www.baldyweb.com/Bookmark.htm

Paul

------ Original Message ------

From: "Dave Williams" <davewillgmale@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 2/22/2021 4:01:09 PM

Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Jim,

That looks very simple but I don't see the relevance to my problem. I don't want to open another form, which would open at its first record. I just want to change the record already being displayed by my form.

Regards,
Dave

On 22/02/2021 22:36, Jim Wagner wrote:

Dave,

I created this solution a long time ago but it works.

I have a form with mini subforms that break out tasks sort of like a card in task apps. when i click on the TaskId in one of the suborms a macro is executied

If IsNull([TaskID])

Beep

End If

If Not IsNull([TaskID])

OpenForm

form Name "name of the form goes here"

view Form

Where Condition =="[TaskID]=" & [TaskID]

windows mode Normal

End if

OnError

Go to Next

Macro Name

Requery

Control Name

hope that gives you some ideas or an approach

good luck

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Monday, February 22, 2021 2:44 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] How to make a form go to a record with a particular ID

I have created a form to display photos from various folders on my PC. The record source is a table as follows:
Name: Photos
Field Name, Data Type
ImageID, Autonumber
Folder, Text
Photo, Text
It contains 100 records e.g.
38, 14/07/2018, 13880718, PICT0001.JPG

This works fine when using the normal navigation buttons, but as there are so many records I have added a list box containing the years, each with the appropriate starting record ID, contained in a table as follows:

Name: FirstPhotoOfYear
Field Name, Data Type
Year, Text
1st photo ID, Number

5 records e.g.
2018, 38

So when I select 2018 in the list box, I want to make the form show the record with ID = 38. I thought it might help to add a text box (called Text13) on the form that shows the ID number produced by the list box (called List11). This shows the ID fine, using a suitable expression as its control source.
In code which operates when the list box is updated, I first use Text13.Requery, followed by DoCmd.FindRecord but without success. I don't know how to make it search just the ID field, or even if it's found anything, as the form does not change to a new record.

I await suggestions.
.

Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Dave,

I am so glad you have it working. I know the feeling of relief when you find a solution.

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Tuesday, February 23, 2021 10:32 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Jim,

Thanks. I had to do a bit more to stop Text15 ending up with a flashing cursor. My code now looks like:

Private Sub List11_AfterUpdate()
Me.Painting = False
Text13.Requery
Text15.SetFocus
DoCmd.FindRecord Text13, acEntire, False, acSearchAll, True, acCurrent, True
List11.SetFocus
Me.Painting = True
End Sub

I was a bit concerned that as both text boxes are still enabled, they could be selected by the cursor, but strangely this is not the case.

Dave W

On 23/02/2021 12:51, Jim Wagner wrote:

Dave

If you do not need to see the controls. Make the background transparent and the font color the same color as the form. That way it is there but not visible to the eye

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Tuesday, February 23, 2021 4:17 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Paul,
Thanks for that - it could be useful, but I've found a solution now. I added another text box (called Text15) to the form, with its data source as the ID field of the table to which the form is bound. My code for List11 is now:

Text13.Requery
Text15.SetFocus
DoCmd.FindRecord Text13, acEntire, False, acSearchAll, True, acCurrent, True

The only trouble is that I can't make Text13 invisible because then it can't be found, and I can't make Text15 invisible because then it can't take focus.
Text13 data source is =DLookUp("[1st photo ID]","[FirstPhotoOfYear]","[Year]=[List11]")

Dave

On 23/02/2021 01:34, Paul Baldy wrote:

The code below can easily be adapted to work on the same form instead of opening another. You can also try the combo box wizard and choose the third option, "Find a record...". You'll only see that option if the form is bound to a table though, if memory serves. The wizard will use a bookmark as well, or at least it used to.

http://www.baldyweb.com/Bookmark.htm

Paul

------ Original Message ------

From: "Dave Williams" <davewillgmale@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 2/22/2021 4:01:09 PM

Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Jim,

That looks very simple but I don't see the relevance to my problem. I don't want to open another form, which would open at its first record. I just want to change the record already being displayed by my form.

Regards,
Dave

On 22/02/2021 22:36, Jim Wagner wrote:

Dave,

I created this solution a long time ago but it works.

I have a form with mini subforms that break out tasks sort of like a card in task apps. when i click on the TaskId in one of the suborms a macro is executied

If IsNull([TaskID])

Beep

End If

If Not IsNull([TaskID])

OpenForm

form Name "name of the form goes here"

view Form

Where Condition =="[TaskID]=" & [TaskID]

windows mode Normal

End if

OnError

Go to Next

Macro Name

Requery

Control Name

hope that gives you some ideas or an approach

good luck

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Monday, February 22, 2021 2:44 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] How to make a form go to a record with a particular ID

I have created a form to display photos from various folders on my PC. The record source is a table as follows:
Name: Photos
Field Name, Data Type
ImageID, Autonumber
Folder, Text
Photo, Text
It contains 100 records e.g.
38, 14/07/2018, 13880718, PICT0001.JPG

This works fine when using the normal navigation buttons, but as there are so many records I have added a list box containing the years, each with the appropriate starting record ID, contained in a table as follows:

Name: FirstPhotoOfYear
Field Name, Data Type
Year, Text
1st photo ID, Number

5 records e.g.
2018, 38

So when I select 2018 in the list box, I want to make the form show the record with ID = 38. I thought it might help to add a text box (called Text13) on the form that shows the ID number produced by the list box (called List11). This shows the ID fine, using a suitable expression as its control source.
In code which operates when the list box is updated, I first use Text13.Requery, followed by DoCmd.FindRecord but without success. I don't know how to make it search just the ID field, or even if it's found anything, as the form does not change to a new record.

I await suggestions.
.

Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

On 23/02/2021 12:51, Jim Wagner wrote:

Dave

If you do not need to see the controls. Make the background transparent and the font color the same color as the form. That way it is there but not visible to the eye

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Tuesday, February 23, 2021 4:17 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Paul,
Thanks for that - it could be useful, but I've found a solution now. I added another text box (called Text15) to the form, with its data source as the ID field of the table to which the form is bound. My code for List11 is now:

Text13.Requery
Text15.SetFocus
DoCmd.FindRecord Text13, acEntire, False, acSearchAll, True, acCurrent, True

The only trouble is that I can't make Text13 invisible because then it can't be found, and I can't make Text15 invisible because then it can't take focus.
Text13 data source is =DLookUp("[1st photo ID]","[FirstPhotoOfYear]","[Year]=[List11]")

Dave

On 23/02/2021 01:34, Paul Baldy wrote:

The code below can easily be adapted to work on the same form instead of opening another. You can also try the combo box wizard and choose the third option, "Find a record...". You'll only see that option if the form is bound to a table though, if memory serves. The wizard will use a bookmark as well, or at least it used to.

http://www.baldyweb.com/Bookmark.htm

Paul

------ Original Message ------

From: "Dave Williams" <davewillgmale@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 2/22/2021 4:01:09 PM

Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Jim,

That looks very simple but I don't see the relevance to my problem. I don't want to open another form, which would open at its first record. I just want to change the record already being displayed by my form.

Regards,
Dave

On 22/02/2021 22:36, Jim Wagner wrote:

Dave,

I created this solution a long time ago but it works.

I have a form with mini subforms that break out tasks sort of like a card in task apps. when i click on the TaskId in one of the suborms a macro is executied

If IsNull([TaskID])

Beep

End If

If Not IsNull([TaskID])

OpenForm

form Name "name of the form goes here"

view Form

Where Condition =="[TaskID]=" & [TaskID]

windows mode Normal

End if

OnError

Go to Next

Macro Name

Requery

Control Name

hope that gives you some ideas or an approach

good luck

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Monday, February 22, 2021 2:44 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] How to make a form go to a record with a particular ID

I have created a form to display photos from various folders on my PC. The record source is a table as follows:
Name: Photos
Field Name, Data Type
ImageID, Autonumber
Folder, Text
Photo, Text
It contains 100 records e.g.
38, 14/07/2018, 13880718, PICT0001.JPG

This works fine when using the normal navigation buttons, but as there are so many records I have added a list box containing the years, each with the appropriate starting record ID, contained in a table as follows:

Name: FirstPhotoOfYear
Field Name, Data Type
Year, Text
1st photo ID, Number

5 records e.g.
2018, 38

So when I select 2018 in the list box, I want to make the form show the record with ID = 38. I thought it might help to add a text box (called Text13) on the form that shows the ID number produced by the list box (called List11). This shows the ID fine, using a suitable expression as its control source.
In code which operates when the list box is updated, I first use Text13.Requery, followed by DoCmd.FindRecord but without success. I don't know how to make it search just the ID field, or even if it's found anything, as the form does not change to a new record.

I await suggestions.
.

Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Dave

If you do not need to see the controls. Make the background transparent and the font color the same color as the form. That way it is there but not visible to the eye

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Tuesday, February 23, 2021 4:17 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Paul,
Thanks for that - it could be useful, but I've found a solution now. I added another text box (called Text15) to the form, with its data source as the ID field of the table to which the form is bound. My code for List11 is now:

Text13.Requery
Text15.SetFocus
DoCmd.FindRecord Text13, acEntire, False, acSearchAll, True, acCurrent, True

The only trouble is that I can't make Text13 invisible because then it can't be found, and I can't make Text15 invisible because then it can't take focus.
Text13 data source is =DLookUp("[1st photo ID]","[FirstPhotoOfYear]","[Year]=[List11]")

Dave

On 23/02/2021 01:34, Paul Baldy wrote:

The code below can easily be adapted to work on the same form instead of opening another. You can also try the combo box wizard and choose the third option, "Find a record...". You'll only see that option if the form is bound to a table though, if memory serves. The wizard will use a bookmark as well, or at least it used to.

http://www.baldyweb.com/Bookmark.htm

Paul

------ Original Message ------

From: "Dave Williams" <davewillgmale@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 2/22/2021 4:01:09 PM

Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Jim,

That looks very simple but I don't see the relevance to my problem. I don't want to open another form, which would open at its first record. I just want to change the record already being displayed by my form.

Regards,
Dave

On 22/02/2021 22:36, Jim Wagner wrote:

Dave,

I created this solution a long time ago but it works.

I have a form with mini subforms that break out tasks sort of like a card in task apps. when i click on the TaskId in one of the suborms a macro is executied

If IsNull([TaskID])

Beep

End If

If Not IsNull([TaskID])

OpenForm

form Name "name of the form goes here"

view Form

Where Condition =="[TaskID]=" & [TaskID]

windows mode Normal

End if

OnError

Go to Next

Macro Name

Requery

Control Name

hope that gives you some ideas or an approach

good luck

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Monday, February 22, 2021 2:44 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] How to make a form go to a record with a particular ID

I have created a form to display photos from various folders on my PC. The record source is a table as follows:
Name: Photos
Field Name, Data Type
ImageID, Autonumber
Folder, Text
Photo, Text
It contains 100 records e.g.
38, 14/07/2018, 13880718, PICT0001.JPG

This works fine when using the normal navigation buttons, but as there are so many records I have added a list box containing the years, each with the appropriate starting record ID, contained in a table as follows:

Name: FirstPhotoOfYear
Field Name, Data Type
Year, Text
1st photo ID, Number

5 records e.g.
2018, 38

So when I select 2018 in the list box, I want to make the form show the record with ID = 38. I thought it might help to add a text box (called Text13) on the form that shows the ID number produced by the list box (called List11). This shows the ID fine, using a suitable expression as its control source.
In code which operates when the list box is updated, I first use Text13.Requery, followed by DoCmd.FindRecord but without success. I don't know how to make it search just the ID field, or even if it's found anything, as the form does not change to a new record.

I await suggestions.
.