Simple lookup form/subform changing subform table records??! 1

[hlnewman]

Hey all--

I've got an embarrassingly simple project to complete in Access that's making me feel like a real dummy. I hope you can help.

I'm trying to use two tables in a form/subform setup to allow users to look up and change records in the subform's table. The parent table is a list of managers; the subform table is a list of payments made by those managers to consultants. I'd like to allow each manager to look up payments made by themselves in the subform table and make changes to those payments.

I've tried setting up a form/subform linking on the manager name in each table, and it works fine in terms of bringing up the right records in the subform. But changing the entry in the main form, while it brings up the right list of records, also changes the DATA in the first row of the subform table so that the manager name is what was selected in the drop-down box. This changes the first row of data in the subform table, regardless of who the manager for that payment is, so it's introducing errors into my data.

This occurs even when the subform is set to read-only. Setting the whole form to read-only doesn't allow a manager name to be selected from the combo box at all.

What am I doing wrong here? Help!

Heather

P.S. If it makes any difference, the database is in Access 2000 operating in a Windows 2000 environment, though I'm replicating the same errors with the same database on my Office 2003/Windows XP machine.

 

[Onyxpurr]

I usually create two fields on the form for this.

#1 Is the combo box that "finds" the record you're looking for and is unbound.

#2 Is a bound text form but locked.

 

[hlnewman]

So how do the two boxes interact with one another? And is the recordset that provides the "answers" still in a separate subform, and if so, what's the relationship between the subform and the text box - because that's what's linked, right?

Sorry to be so confused.

H

 

[JediBMC]

If I am reading your question correctly, you have the following set up:

You have 2 tables, lets call them Table1 and Table2.

Table1 contains Manager information and Table2 contains payments to consultants. Both of these tables include a field representing the manager. I will call that field MngrID.

On your form, lets call it Form1, with a record source of Table1. On Form1 you have several objects, including a combo box with a control source of Table1.MngrID and a subform that we will call SubForm1. SubForm1's record source is Table2.

The Master/Child relationship between Form1 and SubForm1 is set to the MngrID field from Table1 and Table2.

The problem you are experiencing is that then you select a manager in the combobox on Form1, it changes the MngrID for the record to whatever you selected. If I am correct, then here is the solution:

The field you use to select the manager name should be an unbound field. The first thing that you will have to do is change the current record on Form1 to the appropriate record for the manager. To do this, the following code should be assigned to the after update event:

    Me.RecordsetClone.FindFirst _
         "[MngrID] = " & Me![CMBMngrIDSelect]
    Me.Bookmark = Me.RecordsetClone.Bookmark

Additionally, you may want to create a new MngrID field that has a control source of Table1.MngrID. If having this additional field is confusing, simple set the visible property to False.

Ok, now set the subform objects Master/Child Link to the MngrID field.

I hope this helps!

-Brian-
I'm not an actor, but I play one on TV.

 

[JediBMC]

Man I wish there was an edit button. In my previous post, CMBMngrIDSelect is the name of the combobox that you will use to select the manager.

And, no need appologize for being confused, I live my life in the state of confusion... It's nice to have company.

-Brian-
I'm not an actor, but I play one on TV.

 

[hlnewman]

I'm finally starting to get it.

Two more questions:

1. Using this method, what will happen when managers who don't have any payments in Table2 attempt to search for their names? Because they won't have an entry in Table2, there won't be a first record to move to, so will I have the same first-line-editing problems? OR will they just get an error?

2. If the combo box for selecting the manager is unbound, then how do I populate it with the correct list of names to choose from? That list can change at any time. My previous attempts at leaving it unbound displayed the first name in the list but didn't allow for a drop-down box/autofill for the other names.

Thanks for all this help. It's wonderful.

Heather

 

[JediBMC]

To answer your questions:

1. The result in SubForm1 depends on if you allow additions to the form. If so, SubForm1 will show as a new record, ready to input the data. If you only allow reading or data, then it will simply show no data, much like a query that returns with no results.

2. Set the Row Source Type of the combobox to Table/Query and the Row Source to a SQL statement that selects the MngrID from the table. To create the SQL statement, click on the build button [...]. This will launch the query builder. In the query builder, add Table1. Select the fields you want to use. The first row should be the MngrID field. Once you have all of the fields you want, close the query builder. When prompted, select Yes, showing that you do want to update the SQL statement for the Row Source field.

The final step is to set the values of the Column Count (This should be the number of fields you selected in the query builder) and Column Widths (Each field selected should have the width defined here seperated by a semicolon, if you want to hide a field, place a width of 0&quot.

I hope this helps!

-Brian-
I'm not an actor, but I play one on TV.

 

[hlnewman]

Thanks! I'll give this a shot tonight.

H

 

[hlnewman]

Okay, I'm getting closer. But I get syntax errors on the After Update code for my scintillatingly named Combo4 combo box. Mine looks like this:

Private Sub Combo4_AfterUpdate()

Me.RecordsetClone.FindFirst_
"[ManagerName] = " & Me![Combo4]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Now what? I owe someone a beer if I can figure this out.

Heather

 

[JediBMC]

If the code you posted is an exact copy, then the problem is in the first line:

Me.RecordSetClone.Findfirst_

This should read:

Me.RecordSetCloen.FindFirst _

The _ is used to break up a single line of code for readability and tells the compiler that the code is continued on the next line.

If that is not the exact code, and the code does not have that problem, it may be helpfull to have the exact text of the error.

-Brian-
I'm not an actor, but I play one on TV.

 

[hlnewman]

In my own code, I had the whole item on one line (just had split it for ease of viewing here):

Me.RecordsetClone.FindFirst &quot;[ManagerName] = &quot; & Me![Combo4] <----------- all on one line

The specific error I'm getting is &quot;Runtime Error 3077: Syntax error (comma) in expression.&quot; The line highlighted by the debugger is the one listed above.

Cheers,

H

 

[JediBMC]

Hmm...
This may have something to do with the value that is returned by Combo4. Try this:

Me.RecordsetClone.FindFirst &quot;[ManagerName] = '&quot; & Me![Combo4] & &quot;'&quot;
Me.Bookmark = Me.RecordsetClone.Bookmark

If the value is a string, then it must be in quotation marks (single in this case).




-Brian-
I'm not an actor, but I play one on TV.

 

[hlnewman]

That did the trick! Should have figured that out for myself, duh. Was too brain-fried last night and not paying attention.

Thank you so much; the fix is working flawlessly, and I'm replicating it for other searches throughout the database. So much for the hours spent tearing my hair out with reference tomes and the MSKB.

Thanks again!

Heather