Trouble Updating Table With Form Combo Boxes Having Expressions In Con

[Dawnit]

Hi there Access Gurus!

I have a data entry form that I want to use to create new records in a table that will be used to update another table. Most of the text and combo boxes work, but I’m having trouble with one text and one combo box that have expressions in their Control Source. I’ve spent about a week now trying to figure out the problem, so I hope someone can point out where I’m going wrong. Screen shot of form in design view attached--instuctions:
Username (case sensitive): ftpguest
Password: claremont1907ft
Open Download folder

What I want is for the problem text and combo boxes identified below to fill in the fields of the underlying table/record source, like all the others on my form, but they don’t. I’ve even tried an update table but that isn’t working either.

Data entry form: “New Description”
Form’s Record Source: Descriptions Retentions table

Problem - BoxID combo box:
Row Source: SELECT [Descriptions Retentions].[BoxID Prefix] FROM [Descriptions Retentions] ORDER BY [Descriptions Retentions].[BoxID Prefix];
Control Source: =IIf([cboFunc]="A/P",1,IIf([cboFunc]="B/L",2,IIf([cboFunc]="Sanit/Cash",3,IIf([cboFunc]="P/R",4,IIf([cboFunc]="Genl Acct",5,"")))))

Problem - RetUpdate text box:
Control Source: =IIf([totalretcombo]="2 yrs",2,IIf([totalretcombo]="C + 2 yrs",2,IIf([totalretcombo]="C + 4 yrs",4,IIf([totalretcombo]="C = 5 yrs",5,IIf([totalretcombo]="7 yrs AA",7,"")))))

For reference, these combo box fields work okay:
Function – Row Source: SELECT DISTINCT [Descriptions Retentions].Function FROM [Descriptions Retentions];
Description – similar to Function
TotRet – similar to Function

Here’s my VBA :
Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String

strSQL = " SELECT DISTINCT [Descriptions Retentions].Function FROM [Descriptions Retentions] ORDER BY [Descriptions Retentions].Function;"
cboFunc.RowSource = strSQL

End Sub
Private Sub cboFunc_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String


strSQL = "SELECT DISTINCT [Descriptions Retentions].Description FROM [Descriptions Retentions] "
strSQL = strSQL & " WHERE [Descriptions Retentions].Function = '" & cboFunc & "'"
strSQL = strSQL & " ORDER BY [Descriptions Retentions].Description;"

cboDescription.RowSource = strSQL

strSQLSF = "SELECT * FROM [Descriptions Retentions] "
strSQLSF = strSQLSF & " WHERE [Descriptions Retentions].Description = '" & cboDescription & "'"
End Sub

Private Sub cboDescription_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String


strSQL = "SELECT DISTINCT [Subdescriptions].SubDescrip FROM [Subdescriptions] "
strSQL = strSQL & " WHERE [Descriptions Retentions].Description = '" & cboDescription & "'"


strSQLSF = "SELECT * FROM [Descriptions Retentions] "
strSQLSF = strSQLSF & " WHERE [Descriptions Retentions].Description = '" & cboDescription & "'"
End Sub

Private Sub Form_Close()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim stDocName As String

stDocName = "QuerytoUpdateDescripRetTable"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub

Private Sub Save_Click()
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub
Private Sub Command163_LostFocus()
On Error GoTo Err_Command163_LostFocus

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command163_LostFocus:
Exit Sub

Err_Command163_LostFocus:
MsgBox Err.Description
Resume Exit_Command163_LostFocus

End Sub

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click


If Me.Dirty Then Me.Dirty = False
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

I'm crossing my fingers that someone can help!!!

 

[TheAceMan1]

How are ya Dawnit . . .

[blue]What I want is for the problem text and combo boxes identified below to fill in the fields of the underlying table/record source[/blue]

To save data as you require controls have to be [blue]bound[/blue] (controlsource is FieldName). Your expressions in the controlsource are making them [blue]UnBound![/blue] Either [blue]bound[/blue] the controls or save the values thru code.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Dawnit]

Hi TheAceMan1,

And the code would be... ?

 

[TheAceMan1]

Dawnit . . .

Whats the name of the fields you want to update?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Dawnit]

They would be "BoxID Prefix" and "RetUpdate"

 

[Dawnit]

I figured it out!!!!

The problem was with my update query and unbound text and combo boxes! I copied the expressions in in my unbound text and combo boxes and pasted into the corresponding fields - Update To: row of my update query. Then I bound my text and combo boxes.

It may not seem like you guys helped, but just by posting my problem and getting your questions, I was led to the answer.

Thanks so much!!!!

 

[TheAceMan1]

Dawnit . . .

[blue]UpDate Query[/blue] is where I was headed. Glad you got it!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]