Open form with specific record (1 Viewer)

Garcimat

Member
Local time
Tomorrow, 10:15
Joined
Jun 7, 2022
Messages
67
Hi guys I can’t get it to work, my form opens with a random record.
double click in a list box > get values from columns with global variables (variants)> open form with the record based on the variants…. Coding below


Private Sub lst_ActivePermits_DblClick(Cancel As Integer)

'On Error GoTo ErrorBin_Err



gvar_SWONumber = lst_ActivePermits.Column(0) 'capture value 1 from list box (SWO Number)

gvar_PtfNumber = lst_ActivePermits.Column(1) 'capture value 2 from list box (SWO Date)

DoCmd.Close

DoCmd.OpenForm "frm_PTF2", acNormal, "txt_SWO_Number ='" & gvar_SWONumber And "txt_PTF_Number ='" & gvar_PtfNumber, acFormEdit



'ErrorBin_Exit:

' Exit Sub

'ErrorBin_Err:

' MsgBox Err.Number & " " & Err.Description

' Resume ErrorBin_Exit



End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:15
Joined
Oct 29, 2018
Messages
21,358
Can you post a sample db showing the problem?
 

plog

Banishment Pending
Local time
Today, 18:15
Joined
May 11, 2011
Messages
11,613
Code:
DoCmd.OpenForm "frm_PTF2", acNormal, "txt_SWO_Number ='" & gvar_SWONumber And "txt_PTF_Number ='" & gvar_PtfNumber, acFormEdit

1. You're 'And' isn't inside quote marks.

2. You use single quotes twice, but it should be 4 times.
 

Garcimat

Member
Local time
Tomorrow, 10:15
Joined
Jun 7, 2022
Messages
67
I am getting an error, gvar_swonumber is variant I think it is seen this variable as string because the format is 1111-2022. The form is bring all the records.
 

Garcimat

Member
Local time
Tomorrow, 10:15
Joined
Jun 7, 2022
Messages
67
This one
 

Attachments

  • 6D3CFF28-18C8-4741-9800-AC6449EC86D7.jpeg
    6D3CFF28-18C8-4741-9800-AC6449EC86D7.jpeg
    3.9 MB · Views: 143
  • 40C7ED85-C358-495A-8877-1693351103BB.jpeg
    40C7ED85-C358-495A-8877-1693351103BB.jpeg
    3.9 MB · Views: 125

Garcimat

Member
Local time
Tomorrow, 10:15
Joined
Jun 7, 2022
Messages
67
.
 

Attachments

  • 08DDE262-4A4B-489C-859A-D6D591EFFF05.jpeg
    08DDE262-4A4B-489C-859A-D6D591EFFF05.jpeg
    6.7 MB · Views: 123

Garcimat

Member
Local time
Tomorrow, 10:15
Joined
Jun 7, 2022
Messages
67
I think it is the data type….
 

Attachments

  • 2EDBB92E-ECF1-46F8-B2C5-2B164E98E1D4.jpeg
    2EDBB92E-ECF1-46F8-B2C5-2B164E98E1D4.jpeg
    3.4 MB · Views: 135

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:15
Joined
May 7, 2009
Messages
19,169
you have a table design problem.
what does SWO_Number holds?
why is it Long Text in your table?

filtering on Long Text, you are limited to the first 512
characters.
 

plog

Banishment Pending
Local time
Today, 18:15
Joined
May 11, 2011
Messages
11,613
You have a few issues with your table:

1. Index is a reserved word:


Using those as names makes coding and querying more difficult. I suggest prefixing or suffixing it with what data it represents.

2. Date and Time fields. Instead you store both the date and time in the same field. SWO_Date and SWO_Time should not both exist, all the infromation should go into just one of them. You've done this in another place as well.

3. You shouldn't store calculated values. I bet you don't need PTF_Return_Day. I bet you can determine that day by using just the PTF_Return_Date. Can you use a simple formula using PTF_Return_Date to determine PTF_Return_Day? If so, you don't store it.

These are just the issues I see based on table structure, its possible you have more based on the data in the table (those PTF_Limit_Marker fields seem suspect). I suggest you get your table structure right before moving on to any other issues. Can you complete the relationship tool, expand all the tables so we can see all fields and then post a screenshot of it back here?
 

Garcimat

Member
Local time
Tomorrow, 10:15
Joined
Jun 7, 2022
Messages
67
you have a table design problem.
what does SWO_Number holds?
why is it Long Text in your table?

filtering on Long Text, you are limited to the first 512
characters.
Swo holds 1234-2022
It will never be bigger than this, I thought it would be text because the “-“ in the middle
 

Garcimat

Member
Local time
Tomorrow, 10:15
Joined
Jun 7, 2022
Messages
67
You have a few issues with your table:

1. Index is a reserved word:


Using those as names makes coding and querying more difficult. I suggest prefixing or suffixing it with what data it represents.

2. Date and Time fields. Instead you store both the date and time in the same field. SWO_Date and SWO_Time should not both exist, all the infromation should go into just one of them. You've done this in another place as well.

3. You shouldn't store calculated values. I bet you don't need PTF_Return_Day. I bet you can determine that day by using just the PTF_Return_Date. Can you use a simple formula using PTF_Return_Date to determine PTF_Return_Day? If so, you don't store it.

These are just the issues I see based on table structure, its possible you have more based on the data in the table (those PTF_Limit_Marker fields seem suspect). I suggest you get your table structure right before moving on to any other issues. Can you complete the relationship tool, expand all the tables so we can see all fields and then post a screenshot of it back here?
It is complicated this fields have to be field manually by law…. I can’t for example use time() or date()
 

Garcimat

Member
Local time
Tomorrow, 10:15
Joined
Jun 7, 2022
Messages
67
you have a table design problem.
what does SWO_Number holds?
why is it Long Text in your table?

filtering on Long Text, you are limited to the first 512
characters.
You have a few issues with your table:

1. Index is a reserved word:


Using those as names makes coding and querying more difficult. I suggest prefixing or suffixing it with what data it represents.

2. Date and Time fields. Instead you store both the date and time in the same field. SWO_Date and SWO_Time should not both exist, all the infromation should go into just one of them. You've done this in another place as well.

3. You shouldn't store calculated values. I bet you don't need PTF_Return_Day. I bet you can determine that day by using just the PTF_Return_Date. Can you use a simple formula using PTF_Return_Date to determine PTF_Return_Day? If so, you don't store it.

These are just the issues I see based on table structure, its possible you have more based on the data in the table (those PTF_Limit_Marker fields seem suspect). I suggest you get your table structure right before moving on to any other issues. Can you complete the relationship tool, expand all the tables so we can see all fields and then post a screenshot of it back here?
Thanks I spent my day doing exactly that, today I am fixing all the problems caused by these changes.
 

plog

Banishment Pending
Local time
Today, 18:15
Joined
May 11, 2011
Messages
11,613
It is complicated this fields have to be field manually by law….

I would be amazed if you could reference a specific statute.

...I can’t for example use time() or date()

I never suggested those 2 functions, my comments where about calculating data based on other data you are storing.
 

Users who are viewing this thread

Top Bottom