473,323 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Correct record and PK displayed. First record used when code on form called.

Hi Folks. I'm lost and I can't find a spot where this answer exists. I'm opening a specific form to a specific record. Easy right?

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.OpenForm "frm_WorkOrder", acNormal, , "[ID]=" & [ID], , acWindowNormal
  2.  
And that ID is the primary key...

The form opens up to the correct record. Then I have some code that runs after the form is open, the form displays the correct record, but information from the very first record is run... I'm so confused I almost want to add a bunch of screen shots.

Form says record 3003 and displays record 3003, and runs with information from the first record. I deleted the first record just to see what would happen (it was junk data) and the new first record is what comes up. And I have no idea what caused this.

Interesting additional fact. I moved the open call to an embedded macro. Everything worked perfectly... I moved the open call back into vba, broke again...

If I posted an image here, it would be one of me tearing my hair out.
Oct 29 '15 #1
10 1543
BikeToWork
124 100+
The parameter "[ID]=" & [ID] in your open form procedure may be ambiguous. Call [ID] on the form [txtID] so that your argument is "[ID]=" & [txtID]. See if that makes any difference. I don't know why Access names form controls by field names as default.
Oct 29 '15 #2
jforbes
1,107 Expert 1GB
If you are still having trouble with this after following BikeToWork's recommendation, then you might want to post the code that runs on open of the Form here as that might have the bug in it.
Oct 30 '15 #3
zmbd
5,501 Expert Mod 4TB
MS Access Macro and VBA are two entirely different programing languages and what appear to be the same/equivalent commands between the two are often not the same. We would have to have the XML script from the Macro and the VBA to be able to tell you what the differences are...
Oct 31 '15 #4
I want to apologize for my late response. I appreciate all of your help. I've decided to use a write around. For future reference here is what I found and did.

The VBA code to open the form works perfectly and always did. The correct record opens up. After the form is open, there is a reference issue where the first record is found through code, not the record currently displayed, even when referencing fields that have various names (txtID instead of ID). I attempted both where and filter methods and had the same response. (correct record displayed, incorrect record upon VBA). I also did a decompile and compact and repair incase this was a random bug that creeps up from time to time.

When I gave up on the VBA direct code and instead called the MS Access Macro for opening only, then had that macro call the other VBA code I needed, the correct record was displayed and the correct record was accessed through VBA.

In conclusion, I couldn't go through it (VBA) so I went around it (Macro).

Once more, thank you for your assistance.
Nov 3 '15 #5
zmbd
5,501 Expert Mod 4TB
The VBA code to open the form works perfectly and always did. The correct record opens up. After the form is open, there is a reference issue where the first record is found through code, not the record currently displayed, even when referencing fields that have various names (txtID instead of ID).
What is that VBA code?

From what you've given us I cannot duplicate what you are seeing via VBA in my test database.

In conclusion, I couldn't go through it (VBA) so I went around it (Macro).
I'm not sure that's a fair assessment of the situation, I've yet to see something in a form (well outside of the navigationformcontrol and that's a glitch) that required a macro to accomplish. In fact other than the autokeys and autoexec VBA is usually superior to the Macro.

You've only given us part of the picture and there maybe something going on in your code that is clearing the form's conditional or that is directly accessing the underlying query/table and not the form's current snapshot.

Unless you will submit the aforementioned code, we'll not be able to help and it seems a shame to have to use such a clunky workaround :)
Nov 3 '15 #6
First let me say that my comment was in no way a indictment of VBA. Only of my ability to figure out the problem. Per your request I am posting significant portions of my code. I fully agree it is clunky to build a separate macro to do this correctly.


This is on a form I use to order my records for priority testing. Clicking on this opens the specific testing request. This appears to work perfectly. The correct record is displayed on the destination form.
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandStart_Click()
  2. On Error GoTo CommandStart_Click_Err
  3. DoCmd.SetWarnings False
  4.     'lock the record
  5.     DoCmd.RunSQL "UPDATE dbo_WorkOrders SET dbo_WorkOrders.Status = 1 WHERE (((dbo_WorkOrders.ID)=" & [txtID] & ") AND ((dbo_WorkOrders.Status)=0));"
  6.     Call Result_Connection([txtID])
  7.     'open the record
  8.     DoCmd.OpenForm "frm_WorkOrder", acNormal, , "[ID]=" & [txtID], , acNormal, "'" & [txtID] & "'"
  9.     DoCmd.Close acForm, "frm_TestList_InOrder", acSaveYes
  10. CommandStart_Click_Exit:
  11.     Exit Sub
  12.  
  13. CommandStart_Click_Err:
  14.     MsgBox Error$
  15.     Resume CommandStart_Click_Exit
  16. Resume
  17. End Sub
  18.  
I then manually click this button. And the first record is found with the Form_frm_WorkOrder.ID (and all other Form_frm_WorkOrder references). Not the displayed record. Which is quite confusing to me.

Expand|Select|Wrap|Line Numbers
  1. Public Sub cmdAdd_Test_Click()
  2. On Error GoTo cmdAdd_Test_Click_Err
  3.  
  4. Dim countER As Integer
  5. Dim TestsNeeded As Integer
  6. Dim responCE As Integer
  7. Dim mySQL As String
  8. Dim mySQL1 As String
  9. Dim STRpart As String
  10. DoCmd.SetWarnings False
  11. TestsNeeded = DCount("*", "dbo_TestSetLine", "[TestSetKey]=" & Form_frm_WorkOrder.TestKey)
  12. countER = DCount("*", "dbo_WO_Results", "[WorkOrderKey]=" & Form_frm_WorkOrder.ID)
  13. If (Form_frm_WorkOrder.Qty * TestsNeeded) < countER Then
  14.     responCE = MsgBox("There are " & Qty & " parts, each requiring " & TestsNeeded & "tests. This should be " & _
  15.         Qty * TestsNeeded & " records." & vbNewLine & "Record count indicates " & countER & " records." & vbNewLine & _
  16.         "Do you want to add another set of tests?", vbYesNo + vbDefaultButton2, "Enough tests listed already")
  17.     If responCE = vbYes Then
  18.         responCE = CInt(InputBox("How many more sets of tests would you like added?" & vbNewLine & "Remember there are " & TestsNeeded & " tests in the test set." & _
  19.                 vbNewLine & "Whole numbers greater than 0 and less than 11 only please.", "Additional Sets", 1))
  20.         If responSE < 1 Then
  21.             MsgBox "Unable to add, you have entered a number less than 1", vbInformation, "No Additional Test Records created"
  22.             GoTo cmdAdd_Test_Click_Exit
  23.         ElseIf responCE > 10 Then
  24.             MsgBox "Unable to add, you have entered a number great than 10", vbInformation, "No Additional Test Records created"
  25.             GoTo cmdAdd_Test_Click_Exit
  26.         End If
  27. Else
  28.     If TestsNeeded > 0 Then
  29.         responCE = (Form_frm_WorkOrder.Qty * TestsNeeded - countER) \ TestsNeeded
  30.     Else
  31.         responCE = 0
  32.     End If
  33. End If
  34. 'if I get a set from the dbxl page to start with I need to run it the same number of times but at one higher set
  35. If responCE < Form_frm_WorkOrder.Qty Then
  36.     mySQL = "UPDATE dbo_WO_Results SET dbo_WO_Results.ResultComment = 'Part " & Qty & _
  37.             "' WHERE (((dbo_WO_Results.WorkOrderKey)=" & Form_frm_WorkOrder.ID & ") AND ((dbo_WO_Results.ResultComment)='PartA'));"
  38.     DoCmd.RunSQL mySQL
  39. Else
  40.  
  41. End If
  42.     While responCE > 0
  43.         STRpart = "Part " & responCE
  44.         mySQL = "INSERT INTO dbo_WO_Results ( TestKey, ResultText, WorkOrderKey, ResultComment ) " & _
  45.             "SELECT qryPopTests.TestKey, qryPopTests.Unit, " & Form_frm_WorkOrder.ID & " AS Expr1, '" & STRpart & "' AS Expr2 " & _
  46.             "FROM qryPopTests GROUP BY qryPopTests.TestKey, qryPopTests.Unit " & _
  47.             "HAVING (((Count(qryPopTests.TestKey))>1) AND ((Count(qryPopTests.Unit))>1));"
  48.         mySQL1 = "INSERT INTO dbo_WO_Results ( TestKey, ResultText, WorkOrderKey, ResultComment ) " & _
  49.             "SELECT qryPopTests.TestKey, qryPopTests.Unit, " & Form_frm_WorkOrder.ID & " AS Expr1, '" & STRpart & "' AS Expr2 " & _
  50.             "FROM qryPopTests LEFT JOIN qryDups_in_qryPopTests ON qryPopTests.[TestKey] = qryDups_in_qryPopTests.[TestKey] " & _
  51.             "WHERE (((qryDups_in_qryPopTests.TestKey) Is Null));"
  52.  
  53.         DoCmd.RunSQL mySQL
  54.         DoCmd.RunSQL mySQL1
  55.         responCE = responCE - 1
  56.     Wend
  57.  
  58. Form_frm_WorkOrder.dbo_WO_Results_subform.Requery
  59. cmdAdd_Test_Click_Exit:
  60.  
  61.     Exit Sub
  62.  
  63. cmdAdd_Test_Click_Err:
  64.     MsgBox Error$
  65.     Resume cmdAdd_Test_Click_Exit
  66. Resume
  67. End Sub
  68.  

By replacing the line
Expand|Select|Wrap|Line Numbers
  1.     DoCmd.OpenForm "frm_WorkOrder", acNormal, , "[ID]=" & [txtID]
with a macro, the second button refers to the correct record, the displayed record.

Thank you for your patience and help.
Nov 4 '15 #7
zmbd
5,501 Expert Mod 4TB
Sorry, been a very busy day in the lab and it's late here so only a quick glance at our posted code.

What I am not clear about is in the second code block cmdAdd_Test_Click() - Is this the on_click event for a command button on [frm_WorkOrder]?

IF so, then try the construct:
TestsNeeded = DCount("*", _
"dbo_TestSetLine", _
"[TestSetKey]=" & _
ME.TestKey)
;
thus, replacing the form_frm_WorkOrder with the Me to ensure that you are referring to the current instance of the form wherein the event is being called from - normally the construct form_[formname] will work; however, I've ran across some unusual glitches where a second copy of the named form was created.
The other workaround if the event is not on the same form:
Forms![formname]![controlname].value
(even though value is normally implied, I like to explicitly declare this :) ) which should refer to the named control on the named form currently loaded - subforms therein would have a similar construct
Forms!Mainform!Subform1.Form!ControlName

In either case, I suspect this is what the macro has done and why it works there and not in the VBA code. In ACC2010 and newer, you can cut and paste the ACC-Macro to a text file or within a post (design-view, <ctrl><a><ctrl><c> to copy to clipboard, <ctrl><v> to paste from clipboard). You can also convert he Macro to VBA using the conversion wizard... often this creates some horrid code; however, you get to see the logic behind the Macro interpreter.

Just for your reference, you might want to download and print the hardcopy of reference constructs from here: MVP:Refer to Form and Subform properties and controls
Nov 5 '15 #8
jforbes
1,107 Expert 1GB
Something else that concerns me is the OpenArg sent to the Form which usually means there is some code that runs on Load. Which could be causing the unwanted behavior:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frm_WorkOrder", acNormal, , "[ID]=" & [txtID], , acNormal, "'" & [txtID] & "'"
It probably isn't causing your troubles, but it makes me nervous.
Nov 5 '15 #9
BikeToWork
124 100+
Expand|Select|Wrap|Line Numbers
  1.     DoCmd.OpenForm "frm_WorkOrder", acNormal, , "[ID]=" & [txtID], , acNormal, "'" & [txtID] & "'"
Is ID actually a text field? If so the where condition should probably be
Expand|Select|Wrap|Line Numbers
  1. "[ID] = '" & Me.txtID & "'"
Nov 5 '15 #10
zmbd
5,501 Expert Mod 4TB
BikeToWork and JF : Both very good catches, should have seen that myself - too many hours yesterday staring at instrument displays :)
Nov 5 '15 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: kevin.jonas | last post by:
What is the line of code to select the first record of a subform? I have a form with two subforms. What you select in one subform controls what is queried in the second subform. however when the...
5
by: Lyn | last post by:
Hi, I hope someone can help. I have a main form which mostly fills the Access window. In the bottom half of this form I have a tab control to display various types of data related to the main...
1
by: Alex | last post by:
I have a form that when the user click's the "What's This" question mark and the form's section, a pop-up help screen appears. I would like to automatically display this pop-up help context when...
2
by: Anand Ganesh | last post by:
Hi All, How to Implement Move Next, Move Previous, Last Record and First Record in a DataGrid Control ? Is there any standard tool available in the tool box button? Thanks for your time. ...
1
by: Terry | last post by:
I've seen several posts from people who have seen this flashing in TreeView's when resizing a form. I've noticed it in my app, but only in the child windows. For example, my main form has a...
1
by: Kurt | last post by:
If one has a working subform, adds a new field to the subform's table, then adds the field (control) to the subform, MS Access 2003 (11.6566.6568) SP2 will not see that new field in Form View. The...
5
AccessIdiot
by: AccessIdiot | last post by:
This is kind of an odd request. I have a form with a combo box. If an item isn't in the combo box you can add it by launching a new form (using NotInList). I would like for the main form to go...
2
by: BobS | last post by:
I have written a form whose function is to delete the record I have entered under the search criteria. The user enters the asset tag in the text box and presses the search cmd box . The record is...
2
by: erel joffe | last post by:
Hi, I want to loop through my records and display/print them in form view. For each record I want to display only the fields that hold data. Null values change between fields and are null,0 or...
13
by: neelsfer | last post by:
It has been 2 solid evenings now of starring at the focus going to the first record in subform "sfOrderICN" on requering a listbox on the parent form. It worked fine when i used a subform...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.