• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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

0
jlcannon
Asked:
jlcannon
  • 7
  • 7
1 Solution
 
Rey Obrero (Capricorn1)Commented:
check first the Record Source of the form "frmAllinfo".

what is it set to ?
0
 
jlcannonAuthor Commented:
record source is not set and in recordset type it is dynaset
0
 
Rey Obrero (Capricorn1)Commented:
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..
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
jlcannonAuthor Commented:
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..
0
 
Rey Obrero (Capricorn1)Commented:
<so why isnt it picking up the new fields..>

which is not picking the new fields?

your query or your form?
0
 
jlcannonAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
<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.
0
 
jlcannonAuthor Commented:
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.
0
 
jlcannonAuthor Commented:
the Form_Load for frmAllInfo is as follows.

Private Sub Form_Load()
    Me.RecordSource = Forms![f_evc_ue_m].[sql]
    Me.Requery
End Sub
0
 
Rey Obrero (Capricorn1)Commented:
were you able to see the codes that was used to populate the form?
0
 
Rey Obrero (Capricorn1)Commented:
ok, looks like your form " record  source" was previously set (in the design view) using the sql of a query,
then set all textboxes control sources.

so try doing the same, then set the control source of the new textboxes to their corresponding fields.

save the form.

open form again in design view and delete the contents of the Record Source property.

save the form.. try your codes again.
0
 
jlcannonAuthor Commented:
the only code in the code window for frmAllInfo is what I posted above. other than that the onclick event in the first post is the only other code..
0
 
Rey Obrero (Capricorn1)Commented:
cross posting, read  my comments on http:#a36964675
0
 
jlcannonAuthor Commented:
Thank you, i was confised as to how to add a data field to this for since it was unbound. but this solution was perfect.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now