RecordSource Error

[scampbell]

Hello all,

I am pretty new to Access and VBA both, but I've been getting along pretty good until now. Currently I am getting an error while trying to set the RecordSource property, but I will explain the larger scope in case I am going about it wrong.

I am helping another coworker to develop a database to track issues. An initial form will be used to display information and accept values for different sections of the larger project. All of these will have unique IDs, say 1, 2, 3...

If there is a problem discovered while checking that section, they user will need to be able to click a button to generate an "issue" form. These are stored in another table. Currently the two tables ("sections" and "issues") are interacting fine with each other, I have a lot of different things viewed back and forth between their forms.

What I would like to do, however, is link the unique ID for each issue to the original section (Something like 1.1, 1.2, etc.) My first idea was to populate the "Associated FAT ID#" field (already exists) automatically when an issue is generated, and then use a query to concatenate the associated ID with the primary key of the issues table.

So I made a button and have written the following code:

Private Sub Generate_Issue_Click()
    DoCmd.OpenForm "FAT ISSUES LOG FORM", , , , , , Me.[ID#]
    Me.RecordSource = Application.CurrentObjectName
    'Me.[Associated FAT ID#] = Me.OpenArgs
    MsgBox (Application.CurrentObjectName)
End Sub

I mean for this to open the "Issues" form, generate a new record, and insert the ID from the parent form into the "Associated FAT ID#" field. If I comment out the field with the RecordSource property, it will open the form correctly. With that line in there, however, I get the error:

Run-time error '2465':

Microsoft access can't find the field 'ID #' referred to in your expression

At first this seemed simple, but I can't find where it is looking for that field, and it should exist in the issues form.

Long explanation, but hopefully I gave you everything. Thanks for any help.

[hertfordkc]

I don't see an obvious error, but I'll mention several other things that deserve checking.
Me.RecordSource = Application.CurrentObjectName
Does this give you the result that you expect? You mention an "issues" table and an "issues" and this could be an issue.
Your code addresses updating a form control, but you are seeking to create a new record (while in another form which has the focus???).
If this is true, then you may need to open the recordset (the issues table?) and do
rs.addnew to create the entire record with all of its fields.

[scampbell]

I'll explain what I'm seeing currently as I work through the code. If the code is reduced to only

Private Sub Generate_Issue_Click()
    DoCmd.OpenForm "FAT ISSUES LOG FORM", , , , , , Me.[ID#]
    MsgBox (Me.RecordSource)
End Sub

I can see that after the second form is opened, the first form still has the focus of the code. This is also confirmed by trying to write to the control that I intend to. So I need to change the RecordSource correct? That is what I was trying to do with line 3 of the code in my original post. When that is added, or the following line:

Me.RecordSource = "FAT ISSUES LOG FORM"

That is when I get the error and the debugger points to that line of code. The second form does have an On Current that I thought might have been the problem at first, but it doesn't seem to match with the error as the control the error references isn't used:

Private Sub Form_Current()
    Me.Associated_Questions_to_Bayer.Requery
End Sub

It looked like you understood what I was trying to do: the button should open up the other form, create a new record, and populate one field (Associated FAT ID#) based on the "ID#" field of the first. I'm afraid I don't quite understand what you mean here though

If this is true, then you may need to open the recordset (the issues table?) and do
rs.addnew to create the entire record with all of its fields.

I don't guess I know the difference between RecordSource and RecordSet. Am I using the wrong one for this purpose?

[Rawb]

It looks like your problem might be caused by the fact that you're still focused on the first Form?

If that's the case, you can just use the SetFocus() Method to switch to the new Form.

Additionally, when you're referencing data from another Form, you need to use a "distinguished path" to the data. For example:

You have a Form called "Form1" and one called "Form2"

In both Forms, you have a field called "Username" and you want to just grab the data already entered in Form1 and stuck it into Form2's Field automatically (instead of having the user enter their Username again).

In Form2, you would use the following Default value for your "Username" Field: =[Forms]![Form1]![Username]

This tells Access to look for another Form named Form1 and then use the Field Username.

To answer your next question: A RecordSource is part of a Form Field that tells Access where to look for the data. RecordSet, on the other hand, is a somewhat generic term meaning "A group of Records."

In this case, he's suggesting that you may have to use VBA programming to grab the information you want instead of just setting the Field's RecordSource.

[ajetrumpet]

Hello all,

I am pretty new to Access and VBA both, but I've been getting along pretty good until now. Currently I am getting an error while trying to set the RecordSource property, but I will explain the larger scope in case I am going about it wrong.

I am helping another coworker to develop a database to track issues. An initial form will be used to display information and accept values for different sections of the larger project. All of these will have unique IDs, say 1, 2, 3...

If there is a problem discovered while checking that section, they user will need to be able to click a button to generate an "issue" form. These are stored in another table. Currently the two tables ("sections" and "issues") are interacting fine with each other, I have a lot of different things viewed back and forth between their forms.

What I would like to do, however, is link the unique ID for each issue to the original section (Something like 1.1, 1.2, etc.) My first idea was to populate the "Associated FAT ID#" field (already exists) automatically when an issue is generated, and then use a query to concatenate the associated ID with the primary key of the issues table.

So I made a button and have written the following code:

Private Sub Generate_Issue_Click()
    DoCmd.OpenForm "FAT ISSUES LOG FORM", , , , , , Me.[ID#]
    Me.RecordSource = Application.CurrentObjectName
    'Me.[Associated FAT ID#] = Me.OpenArgs
    MsgBox (Application.CurrentObjectName)
End Sub

I mean for this to open the "Issues" form, generate a new record, and insert the ID from the parent form into the "Associated FAT ID#" field. If I comment out the field with the RecordSource property, it will open the form correctly. With that line in there, however, I get the error:

Run-time error '2465':

Microsoft access can't find the field 'ID #' referred to in your expression

At first this seemed simple, but I can't find where it is looking for that field, and it should exist in the issues form.

Long explanation, but hopefully I gave you everything. Thanks for any help.

This is completely incorrect, in terms of the method you want to use to do what you're doing (let's see if I read this right) ---

1) Open the issues form?
2) Create a new issue record?

if those 2 tasks are backwards, that's fine. Either way, what you should be doing here is running an APPEND query on your "issues" table and throwing the initial data into it that you need. Then, open up your issues form, which should be bound to the same table and specify either a filter field and value, or issue a "gotoRecord" command after opening the form.

I assume you want to add a new issue record, then open the form so the user can fill in the details of it? If that's the case, I've read this right.

Regarding the error you got on this line:

'Me.[Associated FAT ID#] = Me.OpenArgs

"OpenArgs" is a form property (or method, I forget). Anytime you use an "=" sign, you're more than likely assigning a value to something, like a property. Furthermore, you need to get rid of the special characters in any prop name or field name you have. e.g. - "#"

[hertfordkc]

[QUOTE=scampbell;88056]I'll explain what I'm seeing currently as I work through the code. If the code is reduced to only

Private Sub Generate_Issue_Click()
    DoCmd.OpenForm "FAT ISSUES LOG FORM", , , , , , Me.[ID#]
    MsgBox (Me.RecordSource)
End Sub

I can see that after the second form is opened, the first form still has the focus of the code. This is also confirmed by trying to write to the control that I intend to. So I need to change the RecordSource correct? That is what I was trying to do with line 3 of the code in my original post. When that is added, or the following line:
-------------------------------------------------------------------------------------------------------------------------------
struggling. In this code snippet, I think the msgbox(me.recordsource) may have prevented focus moving to the other form. The msgbox should have told you the value of Me.Recordsource. Did you see it? Was it correct?
As one of the gurus pointed out, I was suggesting that you might have to use VBA to update a set of records, but that was based on my not fully understand your design. There may be reasons to do that but there may be reasons to follow the suggestions to go down the SQL path, depending on what you are trying to do and which method you are most comfortable with. Unless you are designing for Google, you won't hear me commenting on efficiency and speed. Your time is the most valuable part of the database.

[Rawb]

What you need to do is change the Focus to the second Form, not change the RecordSource.

Try changing your Sub to the following:

Private Sub Generate_Issue_Click()
    DoCmd.OpenForm "FAT ISSUES LOG FORM", , , , , , Me.[ID#]
    [Forms]![FAT ISSUES LOG FORM].SetFocus()

    MsgBox (Me.RecordSource)
End Sub

[scampbell]

Ok, changing the focus allowed me to open the form and pus in the value from OpenArgs just like I wanted it to do. What is not included in my code right now is creating a new record in the form. What is the best way to do that using the approach I have taken so far?

I have seen many examples of how to add a record, but they seem set up for a different design. Maybe I have started off incorrectly for this purpose, but with the form open it seems easy enough.

I assume it is something of the form [Forms]![correctForm].NewRecord but searching through the VBA methods I haven't found what I'm looking for yet. Should I just navigate past the last record in the form?

Thanks for the help, it has held me in frustration and below meltdown to this point.

*UPDATE*

Nevermind, it looks like I've got it working, using DOCmd.GoToRecord. Thanks again, I'll be back to give kudos as soon as I'm sure it's all taken care of.

Now if only I can clean up the four or five other issues with this I can put it all behind me