asked on

pulling data from tables and populating a form.

I have an access form that I have a button on that pulls data using a query and posting to another form. I have added a couple of new fields to one of the tables but can figure out how to get it to pull the data for those fields. I look at the click event for the button on the form and it goes to the code below. and so I opened the frmAllinfo as referenced in the code and tried to add the boxes for the new fields but I dont get data source choices...

Private Sub Command173_Click()
On Error GoTo Err_Command173_Click

    Dim oApp As Object
    Dim mywhere As String
    Dim mylen As Integer
    Dim sql As String

  '  Set oApp = CreateObject("Excel.Application")
  '  oApp.Visible = True
    'Only XL 97 supports UserControl Property
  '  On Error Resume Next
  '  oApp.UserControl = True
    
    sql = "SELECT T_EVC_UE.*, [SumOfImpact] AS [Customer Impact] "
    sql = sql & "FROM T_EVC_UE LEFT JOIN qryImpactTotal ON T_EVC_UE.[Event#] = qryImpactTotal.[Event#]"
  
    If InStr(1, Me.RecordSource, "WHERE") <> 0 Then
  
        mylen = 0
        mylen = InStr(1, Me.RecordSource, "ORDER") - InStr(1, Me.RecordSource, "WHERE")
        mywhere = Mid(Me.RecordSource, InStr(1, Me.RecordSource, "WHERE"), mylen)
        sql = sql & " " & mywhere & ";"
    Else
        sql = sql & ";"
    End If
    
    Me.sql.Value = sql

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    If InStr(1, sql, "Team") > 0 Then
        sql = "SELECT T_EVC_UE.*, qryImpactTotal.SumOfImpact AS [Customer Impact] "
        sql = sql & "FROM (T_EVC_UE LEFT JOIN qryImpactTotal ON T_EVC_UE.[Event#] = qryImpactTotal.[Event#]) LEFT JOIN tblImpactedTeams ON T_EVC_UE.[Event#] = tblImpactedTeams.[Event#] "
        mylen = 0
        mylen = InStr(1, Me.RecordSource, "ORDER") - InStr(1, Me.RecordSource, "WHERE")
        mywhere = Mid(Me.RecordSource, InStr(1, Me.RecordSource, "WHERE"), mylen)
        sql = sql & " " & mywhere & ";"
        Me.sql.Value = sql
    End If
    
        stDocName = "frmAllInfo"
        DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
    
Exit_Command173_Click:
    Exit Sub

Err_Command173_Click:
    MsgBox Err.Description
    Resume Exit_Command173_Click
    
End Sub

Open in new window

Rey Obrero (Capricorn1)

check first the Record Source of the form "frmAllinfo".

what is it set to ?

jlcannon

ASKER

record source is not set and in recordset type it is dynaset

Rey Obrero (Capricorn1)

without the Record Source, your form is unbound and you can not set the control source of any textboxes in the design view of the form..

jlcannon

ASKER

ok so then that tells me its just dumping all the data from the query to that for.. so why isnt it picking up the new fields..

Rey Obrero (Capricorn1)

<so why isnt it picking up the new fields..>

which is not picking the new fields?

your query or your form?

jlcannon

ASKER

if i just run the query the fields are there. but if I click the button to run the sub the fields are not on the frmAllInfo.

Rey Obrero (Capricorn1)

<but if I click the button to run the sub the fields are not on the frmAllInfo.>

what do you expect, you have an unbound form..

are the other fields displayed in form " frmAllInfo" ?

if they are, open the ' frmAllInfo" in design view and check the codes in the Open or Load event of the form.

jlcannon

ASKER

yes all the other fields are displayed and honestly if I knew what to expect I wouldnt be confused on why things are happening this way. I have never used an unbound form to display information from a query. this was just given to me to add those fields and when i did i needed to get them to show up on the frmAllInfo so this is probably not the way I would have gone about it but its what I have been handed so the condesention of the remark is a bit unwarranted but i do really appreciate the help.

jlcannon

ASKER

the Form_Load for frmAllInfo is as follows.

Private Sub Form_Load()
Me.RecordSource = Forms![f_evc_ue_m].[sql]
Me.Requery
End Sub

Rey Obrero (Capricorn1)

were you able to see the codes that was used to populate the form?

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jlcannon