Recordset not returning any values.

[ajaeger]

I'm running the following code on the 'on open' form event. The form’s data is a table named “Participants”. The two fields from this table that are on the form are ID and RegistrationID. The values of these two fields aren't coming through in my strSQL statment that's bolded below. When I look at the value of strSQL via a MsgBox, I see:

Insert into tblConcatenatePartIDs (RegistrationID, ParticipantID) values ('' ,'')

where I expected to see something like:

Insert into tblConcatenatePartIDs (RegistrationID, ParticipantID) values ('02315438188' ,'72')

Here's the code:

Dim exists_in_new_table As Integer
Dim strSQL As String

strSQL = "Delete from tblConcatenatePartIDs"
DoCmd.RunSQL strSQL

Me.Recordset.MoveFirst
Do While Not Me.Recordset.EOF
exists_in_new_table = DCount("*", "tblConcatenatePartIDs", "RegistrationID='" & Me.RegistrationID & "'")

tblConcatenatePartIDs
If exists_in_new_table = 0 Then
strSQL = "Insert into tblConcatenatePartIDs (RegistrationID, ParticipantID) values ('" & Me.RegistrationID & "' ,'" & Me.ID & "')"
DoCmd.RunSQL strSQL

Else …

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[CautionMP]

I'm running the following code on the 'on open' form event.

On_Open is too soon, the fields in your form have not intialized yet so they have no values?

Try using an event that occurs later, maybe On_Load.

Just a poke in the dark,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[Melagan]

I'm curious to know what happens with this code when you migrate it to the On_Load event. Also, may I suggest using breakpoints in your code so you can see the value of your variables in real-time instead of using message boxes? It works snazzy (just hold your mouse cursor over the variable in your vb window and it will show you anything that's currently stored in your variable at the point you're "pausing" your code)


~Melagan
______
"It's never too late to become what you might have been.

 

[ajaeger]

What's throwing me is that it has been working fine. Just this morning, it stopped working and gave me errors further along in my code (based on the missing IDs in tblConcatenatePartIDs). The only thing I can think of that I did was moving the position of ID and RegistrationID on the form.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[ajaeger]

OK, I haven't touched this since I posted the original question. Now, I just copied the code from on_open to on_load and that worked. So I copied it back to on_open and it worked that way too. For now, I think I'll chaulk it up to computer ghosts. Thanks for your suggestions.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com