Solved: Updating table problem

[gsanghvi20]

Hi All,

I have 2 tables called tblManagerInfo and tblFundInfo. tblManagerInfo and tblFundInfo have a one to many relationship on ManagerName. FundName along with the ManagerName is the PK for tblFundInfo and ManagerName alone is the PK for tblManagerInfo. Now I have 1 form to fill out info of Managers and then on clicking Add Fund button it has to add another fund in the tblFundInfo table with the Manager Name being the the one which I have in the ManagerName text box. Both these forms are bound forms to the respective tables.

The problem is that, that every time I add the Managername, it adds a new Manager name in the ManagerInfo table, but when adding Fund, it essentially goes to the first record on the tblFundInfo table and changes that record - essentially deleting the record and putting a new record under the new ManagerName.

Please let me know what is happening. I am sending the snapshot of the macro code I have used for the Add Fund button.
:help

Thanks,
gsanghvi20

[ibgreat]

Can you change your macro into a SQL query so we can see the relationships more easily. There should be an INSERT INTO statement somewhere and I am not seeing it.

Because your form opens on the first record that is the one being changed.

[gsanghvi20]

Funds form is bound to tblFundInfo and Managers form is bound to the tblManagerInfo table. The fund from doesnt update the manager table but instead of creating a new row of information with the corresponding manager name it basically replaces the first record of the Fund table by the new record.

[ibgreat]

Sorry, I picked up that it was okay when I reread your post. I editted my prior post...take a look.

Apparently you were quicker

[gsanghvi20]

Now, the forms are bound to the tables, thus, I think, there wont be any need of any Insert Into statement (I may be wrong). It essentially opens the table in the form.

[CreganTur]

Personally I don't use macro's because of their limitations. Can we see the VBA behind that macro? Click the Windows button (top left of screen) then Save As->Module

This will make a module out of your macro code, which will show exactly what's going in, but from a VBA view. I ask because I'm wondering if your issue is due to form design, or the code of the macro.


Now, the forms are bound to the tables, thus, I think, there wont be any need of any Insert Into statement (I may be wrong).
This is somewhat dependant on the control source of your form, and/or the control source of the objects on your form. Plus it depends on the updateability of the recordset.

[gsanghvi20]

heres the code..

This is the code for the addfund which takes us to the form which is bound to the tblFundInfo:

'------------------------------------------------------------
' frmViewManager
'
'------------------------------------------------------------
Function frmViewManager()
On Error GoTo frmViewManager_Err
DoCmd.OpenTable "tblFundInfo", acViewNormal, acEdit
If (IsNull(Forms!frmViewManager!ViewGoTo) = True) Then
Beep
MsgBox "Please select a Manager Name.", vbCritical, "Investment Management"
End If
DoCmd.GoToRecord acTable, "tblFundInfo", acNewRec
DoCmd.OpenForm "frmAddFund", acNormal, "", "", , acNormal
DoCmd.Close acTable, "tblFundInfo"

frmViewManager_Exit:
Exit Function
frmViewManager_Err:
MsgBox Error$
Resume frmViewManager_Exit
End Function


And here is the code for Adding a fund after we input all the information in the text boxes/combo boxes on the form which is bound to the Fund table:


'------------------------------------------------------------
' frmAddFund
'
'------------------------------------------------------------
Function frmAddFund()
On Error GoTo frmAddFund_Err
With CodeContextObject
On Error Resume Next
If (.Form.Dirty) Then
DoCmd.RunCommand acCmdSaveRecord
End If
If (.MacroError.Number <> 0) Then
Beep
MsgBox .MacroError.Description, vbOKOnly, ""
Exit Function
End If
On Error GoTo 0
DoCmd.GoToRecord , "", acNewRec
DoCmd.GoToControl "AddFundName"
DoCmd.Close acForm, "frmAddFund"
End With

frmAddFund_Exit:
Exit Function
frmAddFund_Err:
MsgBox Error$
Resume frmAddFund_Exit
End Function

[gsanghvi20]

Ok, Now the problem is that it is replacing the first record (of records aligned in ascending order) with the new record. How about if I insert a dummy record everytime I insert a new record which is always the first, no matter what (like '#Fund Name#').

I tried to do that by calling a function in the Macro of the button above. The code of the function is:

Function InsertIntoTblFundInfo()
DoCmd.RunSQL "Insert into tblFundInfo (ManagerName, FundName) Values ('#Manager Name#','#Fund Name#')"
End Function

The problem is that it doesnt work and says that the function InsertIntoTblFundInfo() does not exist.

Please :help
gsanghvi20

[CreganTur]

Do you have frmAddFund setup as a data entry form? Data Entry forms only allow you to add data- they don't show exisitng records.

If you don't have it setup for data entry, then go to the Data tab of the form's properties and set Data entry to 'Yes'.


The problem is that it doesnt work and says that the function InsertIntoTblFundInfo() does not exist.
That's because what you wrote is not a valid SQL string.

[gsanghvi20]

Mr. CreganTur:

Brilliant! :bow: The problem was that I did not setup data entry to 'Yes'.
Thanks a gazillion man.. :clap: and I got rid of the function too.

gsanghvi20