get id from current line in continuous form

[tagteam]

I am trying to use the id from the current line to update data in another table.

I am using something like railcarid = me.railcarid

but when I do this it gives me the railcar id from the first line in the continuous form, not the current line.

Is there a way to get the id from the current line.
Thanks

[Welshgasman]

I cannot see how?

Private Sub ID_GotFocus()
Debug.Print Me.ID & " " & Me.Client
End Sub

produces
1752 Mrs L.L.Jenkins:275586
1749 Mr G.Davies:275080
1748 Mr G.Davies:275080
as expected?

[tagteam]

yes, I would have expected it to work but it does not.

Here is my full code

Dim DOA As Date   DOA = InputBox("Enter the date you want to use for Date of Arrival", "Date of Arrival", "Enter Date of Arrival")
   Dim RailcarID As Double
   RailcarID = Me.RailcarID
   Dim qddate As DAO.QueryDef
   Dim rsdate As DAO.Recordset
   Set qddate = CurrentDb.QueryDefs("q_TracePlaced_SetDOA")
   qddate!Param1 = RailcarID
   qddate!Param2 = DOA
   qddate.Execute dbFailOnError

The query is like this:

UPDATE AllRailcarData SET AllRailcarData.CDateOfArrival = [Param2]
WHERE (((AllRailcarData.RailcarID)=[Param1]));

The problem is that the railcarID that gets picked up is the railcarID in the first line of the continuous form. I call this from a button that is in each line of the form.

This is the code just above it in the VBA

 Dim TraceLineId As Double   TraceLineId = Me.ID
   Dim qd As DAO.QueryDef
   Dim rs As DAO.Recordset
   Set qd = CurrentDb.QueryDefs("q_TracePlaced_MarkConfirmed")
   qd!Param1 = TraceLineId
   qd.Execute dbFailOnError
   Me.Requery

and it works fine to get the id from that line (Which I call the TraceLineID.

I am not sure why the me.RailcarID is not picking up the id from the current line.

[Gicu]

Where are you running the code? Can you post the entire sub?

Cheers,

[Welshgasman]

Surely railcardid should be Long?

[Gicu]

Have you checked that the name of the control (textbox) bound to the RailCarID is indeed RailCarID?

[tagteam]

Here is the entire sub:

If Me.Confirmed = 0 Then   
  Dim TraceLineId As Double
   TraceLineId = Me.ID
   Dim qd As DAO.QueryDef
   Dim rs As DAO.Recordset
   Set qd = CurrentDb.QueryDefs("q_TracePlaced_MarkConfirmed")
   qd!Param1 = TraceLineId
   qd.Execute dbFailOnError
   Me.Requery
   
   Dim DOA As Date
   DOA = InputBox("Enter the date you want to use for Date of Arrival", "Date of Arrival", "Enter Date of Arrival")
   Dim RailcarID As Long
   RailcarID = Me.RailcarID
   Dim qddate As DAO.QueryDef
   Dim rsdate As DAO.Recordset
   Set qddate = CurrentDb.QueryDefs("q_TracePlaced_SetDOA")
   qddate!Param1 = RailcarID
   qddate!Param2 = DOA
   qddate!Param3 = Me
   qddate.Execute dbFailOnError
   
   Me.Requery
   
   Else
   Dim TraceLineID1 As Double
   TraceLineID1 = Me.ID
   Dim qd1 As DAO.QueryDef
   Dim rs1 As DAO.Recordset
   Set qd1 = CurrentDb.QueryDefs("q_TracePlaced_MarkNotConfirmed")
   qd1!Param1 = TraceLineID1
   qd1.Execute dbFailOnError
   Me.Requery
   End If

I have checked that railcarID is named RailcarID
Doesnt matter if I change double to long, I have tried several, same result

[Welshgasman]

Harder to spot error when you do not indent your code?

Waste of time using code tags also when it is like that?

[tagteam]

I have even written a little code and assigned it to a button

Dim RailcarID As Long
RailcarID = Me.RailcarID
MsgBox "the railcar id is " & RailcarID

and when I click the button I get the correct RailcarID. So, I am not sure why the code above is not getting the correct RailcarID.

[Welshgasman]

Next step.....

Walk though your code LINE BY LINE .after setting a breakpoint.

If you do not know how, check out the link in my signature.

Edit: Your Me.requery is probably NOT going to help?

[tagteam]

Interesting, I moved the setting of the Railcar id to be the first thing, even before the if statement.

Dim RailcarID As Long   RailcarID = Me.RailcarID
If Me.Confirmed = 0 Then
   
  Dim TraceLineId As Double
   TraceLineId = Me.ID
   
   Dim qd As DAO.QueryDe
...........

When I do that and look at the vba
Dim RailcarID As Long - this shows the correct railcarid
RailcarID -this is the correct railcarid = Me.RailcarID - this is not the correct railcarid

[tagteam]

Ok, that seems to make it work.

I moved the dim of the RailcarID and setting the variable to be before the if statement.
It still shows me.RailcarID as the wrong id but further down in the parameters qddate!Param1 = RailcarID that parameter shows the correct RailcarID and so it updates correctly.

Still not sure why it would get the wrong one when in the if statement and in the me.RailcarID declaration.

[Gicu]

The reason, as hinted to by the Welshgasman, is your first Me.Requiry after you run the q_TracePlaced_MarkConfirmed query; that statement brings you to the first record so everything from there is based on that.

Cheers,

[Micron]

If you have to requery for some reason, you'll need to create a recordsetclone, bookmark it, requery, then goto the record you were on by retrieving the bookmark value.

[tagteam]

Yes, Ok, thank you for that.

I removed the me.requery and put the Dim RailcarID back where it was and everything works fine.

So if you are reading this thread it was the the me.requery after the first query that did just as Gicu said and reset the data and then grabbed the first id.