Problem with me.fieldname or autonumber? (1 Viewer)

[PaddyIrishMan]

Hi all,

In my database, I have a field called "Comment". I'm displaying Continuous forms so I don't have a lot of space on screen. With this in mind & as the Comments field is optional I decided I would include it as a Popup - Double click a field & the following code runs:

'Build the SQL string
SQLString =
"SELECT tlb_CurrentTasks.Comment, tlb_CurrentTasks.[Record ID] FROM tlb_CurrentTasks
WHERE (((tlb_CurrentTasks.[Record ID]) = " & Me.Record_ID & "));"

'Open the Popup & set the RecordSource
DoCmd.OpenForm "frmComments", acNormal
Forms!frmComments.RecordSource = SQLString

So this *should* open the Comments form relating to the current record's record_id.
The problem I'm having is that it only works some of the time.
Sometimes it gets the record_id wrong.
The Record_id is an autonumber field & sometimes what happens is that the comments field pops up BUT the comment is entered into an incorrect record - i.e. If I add a comment to the first record while no other records exist, a new record is created in the background (next increment of the autonumber field) & this is where the comment is added.
I'm not sure if I've made much sense above but maybe if you've seen similar issues & if you could offer any suggestions they'd be much appreciated.

Regards,
Patrick

 

[Rob.Mills]

I see a couple of errors

First, since your id is an integer you won't have to surround it with quotes.

Second, since you're setting the recordsource of the comments form you won't be able to use Me to reference the first form. Me will refer to the pop-up form itself.

Try this:

"SELECT tlb_CurrentTasks.Comment, tlb_CurrentTasks.[Record ID] FROM tlb_CurrentTasks
WHERE (((tlb_CurrentTasks.[Record ID]) = [Forms]![FormName]![Record_ID]));"

 

[PaddyIrishMan]

Hi Rob - thanks for replying,
I've tried the new SQL string,
I couldnt get
WHERE (((tlb_CurrentTasks.[Record ID]) = [Forms]![FormName]![Record_ID]));" to work

I kept getting Error msg "Access Cant find the form [formName]"

so I tried using:
WHERE (((tlb_CurrentTasks.[Record ID]) = " & Form_QATeamSubmitSubform.Record_ID & "));"

(QATeamSubmitSubform is the name of the form)

I assume this is the same as your WHERE Clause?
Anyway, It still has the same problems.
Do you think the issue is due to the SQL & not an autonumber problem?

Thanks again,
Patrick

 

[Rob.Mills]

Did you copy this verbatim [Forms]![FormName]![Record_ID]?

Where it says 'FormName' you need to change that to the actual name of the first form.

 

[PaddyIrishMan]

Yep, I changed the Formname to the correct form name. It just doesnt like it very much ;-)

 

[Rob.Mills]

Let's try another approach. Create a variable and use that.

Dim i as integer

i=me!Record_ID

'Build the SQL string
SQLString =
"SELECT tlb_CurrentTasks.Comment, tlb_CurrentTasks.[Record ID] FROM tlb_CurrentTasks
WHERE (((tlb_CurrentTasks.[Record ID]) = & i));"

See if that works.

 

[Mile-O]

Could the problem be here?

Form_QATeamSubmitSubform.Record_ID

WHERE

what you really need is

Forms!QATeamSubmitSubform.Record_ID

 

[PaddyIrishMan]

Thanks again Rob, Close but no cigar though;

The problem I had with the Access Error message - "Access cant find the Form etc." was due to the fact that the fieldname had a space - this was being used in vba as Record_Id whereas SQL required it to be [Record Id]

I've tried using a variable & added some Debug messages, I added a message for the click event of the field to display the record id - the correct one is displayed.
I added another message to the close event of the popup form to display the recordsource - the correct id is also displayed in the recordsource BUT my Comment isnt going into this record????
Bizarre???

 

[Rob.Mills]

Try this. In the pop-up form go ahead and set the recordsource to the name of the table. then just use this command to open it:

DoCmd.OpenForm "FormName", , ,"[Record ID]=" & Me![Record ID]

I think this would make things much simpler.

 

[PaddyIrishMan]

Thanks for your input Mile-O=Phile,
I tried your suggestion but the form doesnt seem to be recognised by the Forms collection.
I can only get access to it by explicitly referencing it as Form_[FormName].

Regards,
Patrick

 

[PaddyIrishMan]

Rob, You have the patience of a saint BUT it still does the same thing. I'll have to take it apart & thoroughly debug it as it's really giving me a headache - on a Friday afternoon of all days!!
If you have any suggestions as to a better way of approaching this (not enough space for a comment field on-screen) then I'd love to hear them. If not, I wont waste any more of your time, thanks a million for your help & I hope you have a great weekend.

Regards,
Patrick.

 

[Rob.Mills]

Wait! Can't stop now. There must be something else underlying here. I mean there's hundreds of access experts here and none of us can figure out how to get a form to open to the correct record? Hey, that sounds like a new joke... How many access programmers does it take to open a form correctly?

I'm going to send you a pm with my email. Send me the db if you want.

 

[Mile-O]

You could do it (rather cheekily and longwinded) through code if you declared a public variable that stored the linking ID and then set the recordsource of the popup form to be a query.

In the query, you could then, on the ID field, have a function called something like, FORMID()

And in a module just through in

Public Function FORMID()

FORMID = the public variable

End Function