Help Please

[seany]

I am trying to make the command open a form then add all the values for typeID and OrderDate together. But it is just putting in the answer for one record

Private Sub Command3_Click()

Dim i As Integer
Dim stDocName As String
Dim stlink As String
Dim s As Integer

stDocName = "Form2"
stlink = "[RoadNo]=" & Me![RoadNo]

For s = 1 To 1 'Number of records
For i = 1 To Me.CountOfRoadNo.Value
DoCmd.OpenForm stDocName, , , stlink
Me.TRoDate.Value = Me.TRoDate.OldValue & Forms!form2!TypeID & "," & Forms!form2!OrderDate
DoCmd.GoToRecord , , acNext
Next
DoCmd.Close
DoCmd.GoToRecord , , acNext
Next

End Sub

 

[namliam]

You are only building the link criteria once....

Try putting your line: stLink =...
inside the for loop somewhere before opening the form...

Regards

The Mailman

 

[seany]

This code is working but it is add the value for s on to the final answer. How can i make t different for each value of s
Private Sub Command3_Click()
Dim j As Integer
Dim i As Integer
Dim stDocName As String
Dim stlink As String
Dim s As Integer
Dim t As String
Dim Prompt As String
Dim Title As String
Dim StrInput As String
Dim r As String

j = InputBox(Prompt:="Number of record to updata?", Title:="Updata", XPos:=2000, YPos:=2000)

stDocName = "GIS_Updatasub"
stlink = "[RoadNo]=" & Me![RoadNo]

For s = 1 To j
t = r & s
DoCmd.OpenForm stDocName, , , stlink
For i = 1 To Me.CountOfRoadNo
t = t & [Forms]![GIS_Updatasub].Type & "," & [Forms]![GIS_Updatasub].OrderDate & ";"
DoCmd.GoToRecord , , acNext
Next
Me.TRoDate.Value = t
DoCmd.Close
DoCmd.GoToRecord , , acNext
Next

End Sub

 

[namliam]

This code is working but it is add the value for s on to the final answer. How can i make t different for each value of s

Private Sub Command3_Click()
    Dim j As Integer
    Dim i As Integer
    Dim stDocName As String
    Dim stlink As String
    Dim s As Integer
    Dim t As String
    Dim Prompt As String
    Dim Title As String
    Dim StrInput As String
    Dim r As String
    
    j = InputBox(Prompt:="Number of record to updata?", Title:="Updata", XPos:=2000, YPos:=2000)
    
    stDocName = "GIS_Updatasub"
    stlink = "[RoadNo]=" & Me![RoadNo]
    
    For s = 1 To j
        t = r & s
        DoCmd.OpenForm stDocName, , , stlink
        For i = 1 To Me.CountOfRoadNo
            t = t & [Forms]![GIS_Updatasub].Type & "," & [Forms]![GIS_Updatasub].OrderDate & ";"
            DoCmd.GoToRecord , , acNext
        Next
        Me.TRoDate.Value = t
        DoCmd.Close
        DoCmd.GoToRecord , , acNext
    Next

End Sub

T is differen for each value of S

R in this code is a NULL value.
therefor if S = 1 then T = "1"
later on you add values from the form:
t = t & [Forms]![GIS_Updatasub].Type & "," & [Forms]![GIS_Updatasub].OrderDate & ";"

when S becomes 2 then T = "2"
etc...

I guess you are not posting all the code... Or i am dimwhitted...

Regards

 

[seany]

I have posted all the code but i have made the changes that you said todo with the lstlink

example of output when s = 1

1waiting, 01/06/83; waiting, 01/09/46;

Why is there a 1 at the start is it because of t = t & s

I can attach a copy of the db if that would help

 

[namliam]

t = r & s
t = t & [Forms]![GIS_Updatasub].Type & "," & [Forms]![GIS_Updatasub].OrderDate & ";"

R = null
S = 1
[Forms]![GIS_Updatasub].Type = waiting
[Forms]![GIS_Updatasub].OrderDate = 01/06/83

Because of the line in blue T becomes 1

Then (the second line) adds waiting, 01/06/83; to T (which allready was 1) becoming 1waiting, 01/06/83

Try stepping thru the code (F8) and seeing what happenes...

Regards

 

[seany]

Thanks namlian,

here is the final working code i had to put in a if statmant so when t = (r & s) it will not add t to the start
Private Sub Command3_Click()
Dim j As Integer
Dim i As Integer
Dim stDocName As String
Dim stlink As String
Dim s As Integer
Dim t As String
Dim Prompt As String
Dim Title As String
Dim StrInput As String
Dim r As String

j = InputBox(Prompt:="Number of record to updata?", Title:="Updata", XPos:=2000, YPos:=2000)
If Not IsNull(j) Then
stDocName = "GIS_Updatasub"

For s = 1 To j
t = r & s
stlink = "[RoadNo]=" & Me![RoadNo]
DoCmd.OpenForm stDocName, , , stlink
For i = 1 To Me.CountOfRoadNo
If t = (r & s) Then
t = [Forms]![GIS_Updatasub].Type & ", " & [Forms]![GIS_Updatasub].OrderDate & "; "
Else: t = t & [Forms]![GIS_Updatasub].Type & ", " & [Forms]![GIS_Updatasub].OrderDate & "; "
End If
DoCmd.GoToRecord , , acNext
Next
Me.TRoDate.Value = t
DoCmd.Close
DoCmd.GoToRecord , , acNext
Next
End If

End Sub

 

[namliam]

NO NO NO NO... wrong way around.... If you dont want the value of S (1) there try this:

Private Sub Command3_Click()
    Dim j As Integer
    Dim i As Integer
    Dim stDocName As String
    Dim stlink As String
    Dim s As Integer
    Dim t As String
    Dim Prompt As String
    Dim Title As String
    Dim StrInput As String
    Dim r As String
    
    j = InputBox(Prompt:="Number of record to updata?", Title:="Updata", XPos:=2000, YPos:=2000)
    If Not IsNull(j) Then
        stDocName = "GIS_Updatasub"
        
        For s = 1 To j
            stlink = "[RoadNo]=" & Me![RoadNo]
            DoCmd.OpenForm stDocName, , , stlink
            For i = 1 To Me.CountOfRoadNo
                t = t & [Forms]![GIS_Updatasub].Type & ", " & [Forms]![GIS_Updatasub].OrderDate & "; "
                DoCmd.GoToRecord , , acNext
            Next
            Me.TRoDate.Value = t
            DoCmd.Close
            DoCmd.GoToRecord , , acNext
        Next
    End If
End Sub

Regards