Do While ... Loop help

[sunset1215]

[Solved] Do While ... Loop help

hi all, i'm trying to add payment records when my clients' status is pending payment.
i came up with this code, but it is giving me an error at this line(highlighted in red), saying that i can't go to the specific record.

i have just realised that this happens because the code only copies the value of [pkXtID] of the first record in my fsubXt. how do i go about making it copy the corresponding ID for the rest of the records in my fsubXt?

code attached:

Private Sub cmdAddXtInc_Click()
Dim status
status = DLookup("strXtStatus", "tblXt", "[fkTrialID] = " & Me.pkTrialID)
 
DoCmd.OpenForm "fsubXtInc", acFormDS, , , acFormEdit, acHidden
Do While status = "Pending Payment"
[COLOR=red]DoCmd.GoToRecord acDataForm, "fsubXtInc", acNewRec[/COLOR]
Forms!fsubXtInc!fkXtID.Value = Forms!frmTrial!fsubXt!pkXtID.Value
Loop
DoCmd.Close acForm, "fsubXtInc", acSaveNo
End Sub

any help or advice is greatly appreciated.

 

[Beetle]

You appear to be trying to insert a foreign key value into a table, which is a bit unusual in an application like Access, as that is usually handled by the Master/Child link in a form/subform, but without knowing your situation I can't really comment. Perhaps you have some legitimate reason.

I can offer the following suggestions;

status = DLookup("strXtStatus", "tblXt", "[fkTrialID] = " & Me.pkTrialID)

Dlookup only returns a single value (the first one it finds that meets the criteria), as you seem to have discovered, so there really is nothing to "loop" through here. If you want to return all the records from the table that have the desired status, you need to instantiate a record set.

DoCmd.OpenForm "fsubXtInc", acFormDS, , , acFormEdit, acHidden

You're opening the form in hidden mode, which means there will apparently be no human interaction involved here. This begs the question - why open the form at all? If you need to modify, or add, records in a table, and the process requires no human interaction then you don't need to open a form. Just use an update or append query within your code. Here is an example of opening a record set and looping through it to perform some action. I don't know exactly what the "something" is that you are attempting to do, so I can't provide any advice along those lines, but this should at least give you an idea how to instantiate a record set and loop through it;

'<code>

Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select * From tblXt Where strXtStatus = 'Pending Payment'"

Set rst = CurrentDb.OpenRecordset (strSQL, dbOpenSnapshot)

With rst
If .RecordCount > 0 Then
.MoveFirst
Do While Not .EOF
'code to grab some field value from the current row and do something
.MoveNext 'move to the next row
Loop
Else
MsgBox "No records found"
End If
End With

Set rst = Nothing

'</code>

If you want to post back with more details, myself or someone else here may be able to help you with the actual code to do whatever it is you are trying to do.

 

[sunset1215]

i'm a beginner to access vba. so i was trying to make things work with my limited knowledge.

i am indeed trying to insert a foreign key into the table. there are 2 tables in question, tblXT and tblXtInc.

tblXt
pkXtID
productname
strXtStatus

tblXtInc
pkXtInxID
fkXtID
payment date
voucher number

tblXt holds the product information that our clients are testing for us, and tblXtInc would be the payment information. i wanted to add records in tblXtInc when the status are 'pending payment', so that i can update the payment information in another form. that is why i thought of copying the primary keys and insert into the foreign keys.

is it really needed to use the recordset in this case? is there another option?

i really appreciate it if you could guide me through this.

 

[Beetle]

is it really needed to use the recordset in this case? is there another option

Yes.There's almost always more then one way to accomplish a given task in Access. My previous suggestion was based on the fact that you appeared to attempting to accomplish this with code, so I was trying to explain how it would need to be done using code. However, you could also do this by simply creating an append query, no code needed.

If I understand correctly, for each row in tblXt where the strXTStatus field = "Pending Payment" you want to grab the primary key (pkXtID) from that row and create a new record in tblXtInc, inserting this value in the foreign key field (fkXtID). As long as the primary key of tblXtInc (pkXtIncID) is an Autonumber data type, then the following SQL should work. You could copy/paste in to your query design grid if you want. If you are going to test this, do so on a backup copy of your database. SQL statement is as follows;

INSERT INTO tblXtInc(fkXtID) SELECT tblXt.pkXtID FROM tblXt WHERE tblXt.strXtStatus = 'Pending Payment'"

 

[sunset1215]

thanks for the suggestion. i have tried it out in my database. it works, but i actually have another table in the mix.

tblTrial
pkTrialID

tblXt
pkXtID
fkTrialID
productname
strXtStatus

tblXtInc
pkXtInxID
fkXtID
payment date
voucher number

i have edited the code to look like below, but it still appends the same number of rows as the code you provided. any ideas?

not sure if this has any effect, but my frmTrial is opened as a dialog, and tblXt is the subform of frmTrial. i have a command button on frmTrial that will execute this code.

Dim SQL
SQL = 
"INSERT INTO tblXtInc(fkXtID) " & _
"SELECT tblXt.pkXtID " & _
"FROM tblTrial INNER JOIN tblXt ON tblTrial.pkTrialID = tblXt.fkTrialID " & _
"WHERE (((tblXt.strXtStatus)='Pending Payment') AND
((tblXt.fkTrialID)=[tblTrial].[pkTrialID]))"
 
DoCmd.RunSQL SQL

 

[sunset1215]

hi, i finally got some time to try again. and i have gotten it to work. thanks a lot, Beetle, for helping me out.

here's the final working code:

Private Sub cmdAddXtInc_Click()
Dim SQL As String
SQL = "INSERT INTO tblXtInc(fkXtID) " & _
      "SELECT tblXt.pkXtID " & _
      "FROM tblTrial INNER JOIN tblXt ON tblTrial.pkTrialID = tblXt.fkTrialID " & _
      "WHERE (((tblXt.strXtStatus)='Pending Payment') " & _
      "AND ((tblXt.fkTrialID)=[forms]![frmTrial]![pkTrialID]))"
 
DoCmd.RunSQL SQL
End Sub