serious help - #Name error in txtHosp whenever this code is ran

[teneesh]

I am trying to get rid of the #Name error that appears in my txtHosp text box when I run this code. the code below is also a call command, so any time I am updating a combo box, the following error appears in my textbox. I noticed that when I removed the code line:
Me.RecordSource = sqltext it takes it away and works fine, however then my other comboboxes do not load appropriately. seems like i'm overlooking something very obvious and i can't seem to find it.

Dim sqltext As String
 
    '---- Form datasource
   sqltext = "Select * from confattntracktest " & _
        "where acyear=" & Me.cbxAcYear & _
        " and rotation=" & Me.cbxMonth & _
        " and conference='" & Me.cbxConfType & "'" & _
        " and hospital='" & Me.cbxHosp & "'"
  Me.RecordSource = sqltext
    Me.txtAcYear.DefaultValue = Me.cbxAcYear
    Me.txtMonth.DefaultValue = Me.cbxMonth
    Me.txtConference.DefaultValue = Me.cbxConfType
    Me.txtHosp.DefaultValue = Me.cbxHosp
    '---- Resident box datasource
    sqltext = "select eid,dbo.idtoname(eid) resname " & _
        "from schedule s,serviceward w " & _
        "where stfgroup in (3,4) " & _
        "and rotation=" & Me.cbxMonth & _
        " and acyear=" & Me.cbxAcYear & _
        " and s.srvcode=w.srvcode " & _
        " and hospital=" & Me.cbxHosp & "' " & _
        " order by 2"
    Me.ComboResident.RowSource = sqltext
    Me.Refresh
    Me.Detail.Visible = True
End Sub

[MMcCarthy]

Me.RecordSource = sqltext

This changes the record source of your form. So I believe the problem is that the value being obtained by selecting cbxHosp is not returning the value you want. What is the datatype of the hospital field in your table?

[teneesh]

the data type of hospital in my table is Char. Length = 2.
should I change it to something else?

Me.RecordSource = sqltext

This changes the record source of your form. So I believe the problem is that the value being obtained by selecting cbxHosp is not returning the value you want. What is the datatype of the hospital field in your table?

[MMcCarthy]

the data type of hospital in my table is Char. Length = 2.
should I change it to something else?

Assuming this is a SQL backend.

Check the linked table in design view and see how Access is interpreting this field as Access doesn't have a char datatype.

[teneesh]

yes, backend is sql, where datatype is char, but its also displaying as char in access for the design of the table. however i did just notice that the allow nulls checkbox was checked and it should not be. when i unchecked it, i get another error stating that null values cannot be inserted into the table. 'insert fails'
please advise. thanks

[MMcCarthy]

yes, backend is sql, where datatype is char, but its also displaying as char in access for the design of the table. however i did just notice that the allow nulls checkbox was checked and it should not be. when i unchecked it, i get another error stating that null values cannot be inserted into the table. 'insert fails'
please advise. thanks

Can you change the datatype of the sql to varchar as Access will interpret this as a string. I think it's having a problem interpreting the char datatype.

Essentially you can't make design changes to a linked table in Access they will have to be made in the backend.

[teneesh]

I will try this - thanks so much.

[teneesh]

sending what worked:
I was missing the quotes around cbxHosp.

Sub PopulateData()
Dim sqltext As String
'---- Form datasource
sqltext = "Select * from confattntrack " & _
"where acyear=" & Me.cbxAcYear & _
" and rotation=" & Me.cbxMonth & _
" and conference='" & Me.cbxConfType & "'" & _
" and hosp='" & Me.cbxHosp & "'"
Me.RecordSource = sqltext
Me.txtAcYear.DefaultValue = Me.cbxAcYear
Me.txtMonth.DefaultValue = Me.cbxMonth
Me.txtConference.DefaultValue = Me.cbxConfType
Me.ComboHosp.DefaultValue = "'" & Me.cbxHosp & "'"

'---- Resident box datasource
sqltext = "select eid,dbo.idtoname(eid) resname " & _
"from schedule s,serviceward w " & _
"where stfgroup in (3,4) " & _
"and rotation=" & Me.cbxMonth & _
" and acyear=" & Me.cbxAcYear & _
" and s.srvcode=w.srvcode " & _
"and hosp='" & Me.cbxHosp & "' " & _
" order by 2"
Me.ComboResident.RowSource = sqltext
Me.Refresh
Me.Detail.Visible = True
End Sub

[MMcCarthy]

Glad you got it working. You shouldn't have needed the quotes on the default value setting but this is obviously getting around the fact the Access can't recognise the char value.

If it works it works :D