Results 1 to 15 of 15
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table fields do not get populated (Access2002)

    There must be a difference: in your SQL string

    mSQL = "INSERT INTO Search_Result" _
    & "([EC Member], [Supervisor], [Last Name],[First Name], [Business Unit]," _
    & "[Cost Center])"
    ...

    You only insert values for the fields listed between the parentheses. Pay Date, Amount Charged and Manual Check Date are not among them, so they will not be populated.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table fields do not get populated (Access2002)

    Edited by HansV to present data in table form - see <post#=164109>post 164109</post#>

    Hi!

    Here is another problem I am dealing with.
    I am running code
    Private Sub cmd_Search_Click()

    Dim mSQL As String, ListSQL As String, StrRS As String, txtStr As String

    Set dbR = CurrentDb()
    DoCmd.SetWarnings False

    dbR.Execute "Delete from Search_Result"
    mSQL = "INSERT INTO Search_Result" _
    & "([Pay date],[EC Member], [Supervisor], [Last Name],[First Name], [Business Unit]," _
    & "[Cost Center], [Amount Charged], [Manual Check Date])" _
    & "SELECT [master-data-charges].[Pay date],[master-data-charges].[EC Member]," _
    & "[master-data-charges].[Supervisor], [master-data-charges].[Last Name]," _
    & "[master-data-charges].[First Name], [master-data-charges].[Business Unit]," _
    & "[master-data-charges].[Cost Center], [master-data-charges].[Amount Charged]," _
    & "[master-data-charges].[Manual Check Date]" _
    & "FROM [master-data-charges] WHERE" _
    & "[master-data-charges].[Pay date]=([Forms]![frm_Find_User]![txt_Search])" _
    & "or [master-data-charges].[EC Member]=([Forms]![frm_Find_User]![txt_Search])" _
    & "or [master-data-charges].[Supervisor]=([Forms]![frm_Find_User]![txt_Search])" _
    & "or [master-data-charges].[Last Name] = ([Forms]![frm_Find_User]![txt_Search])" _
    & "or [master-data-charges].[First Name]=([Forms]![frm_Find_User]![txt_Search])" _
    & "or [master-data-charges].[Business Unit]=([Forms]![frm_Find_User]![txt_Search])" _
    & "or [master-data-charges].[Cost Center]=([Forms]![frm_Find_User]![txt_Search])" _
    & "or [master-data-charges].[Amount Charged]=([Forms]![frm_Find_User]![txt_Search])" _
    & "or [master-data-charges].[Manual Check Date] = ([Forms]![frm_Find_User]![txt_Search]);"
    DoCmd.RunSQL mSQL

    '---------------------------------------------------------
    Set rsResp = dbR.OpenRecordset("Search_Result", dbOpenTable)
    On Error Resume Next
    txtStr = rsResp![Last Name]
    If txtStr = "" Then
    MsgBox "Search Produced No Results! Please, try again!"
    End If
    '---------------------------------------------------------


    List_Result.RowSource = "Select [EC Member], [Supervisor], [Last Name],[First Name], [Business Unit], [Cost Center] from Search_Result"

    End Sub

    When I run this code my table gets populated like this
    <table border=1><td>Pay date</td><td>EC Member</td><td>Supervisor</td><td> Last Name</td><td>First Name</td><td>Bunit</td><td>Cost Center</td><td>Amount Charged</td><td>Manual Check Date</td><tr><td align=right>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table fields do not get populated (Access2002)

    Hi
    I edited my post. The error I am getting at line DoCmd.RunSQL mSQL is "Datatype mismatch in criteria expression"
    So my code can not insert those fields Pay Date, Amount Charged and manual Check.
    All of those are mapped exactly as in master-data-charges. Text, Number, Number

    Without those fields I were able to insert.
    THANKS

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table fields do not get populated (Access2002)

    I don't think we can solve that without seeing the database. See <post#=401925>post 401925</post#> for instructions on posting a stripped down copy of a database.

    Note: if I were you, I would try to get to the bottom of your Excel import problem first - perhaps this problem will go away too if you solve that one!

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table fields do not get populated (Access2002)

    Hi!
    Trying to send mdb. Open form and insert 37988 in search. Click Search button
    Thanks a lot

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table fields do not get populated (Access2002)

    Works fine for me - see screenshot. Combined with your Excel import problems, it makes me suspect that your Access installation is corrupt.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table fields do not get populated (Access2002)

    Can you please scren shot last 2 fields? Those are the problemed 2. I will have to show it to ppl to make them reinstall my Access.

    THANKS so much, HansV <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table fields do not get populated (Access2002)

    The Search_Result table is being filled correctly, and I don't get an error message. But there are two problems in your form:
    - You don't include Amount Charged and Manual Check Date in the row source of the list box
    - The Column Count of the list box is set to 8, while there are 9 fields if you include the two mentioned above.
    If you correct this, the list box displays all the data.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table fields do not get populated (Access2002)

    Thanks a million. I just found out that our Access install was Customized, so some features does not included. They will reinstall MS Office now. Good Day and thanks again

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table fields do not get populated (Access2002)

    Hi!
    Now I am entering item in search textbox. Result shown in a Listbox. Then I want to enter another result and if it is Last Name - I am getting error message "Data type mismatch in criteria expression" When it is 37988 it searches.

    Also those 2 problem columns wouldn't show unless I will close and re-open ListBox.
    Thanks again

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table fields do not get populated (Access2002)

    You are trying to compare all fields to the search value. If the search value is a number, that is no problem since text fields can have numeric values. But if the search value is text, trying to compare a number field to a string causes the error message you report. You may be able to get around it by using Like instead of =. Like will compare everything as strings.

    mSQL = "INSERT INTO Search_Result" _
    & " ([Pay date], [EC Member], [Supervisor], [Last Name], [First Name], [Business Unit]," _
    & " [Cost Center], [Amount Charged], [Manual Check Date])" _
    & " SELECT [master-data-charges].[Pay date],[master-data-charges].[EC Member]," _
    & " [master-data-charges].[Supervisor], [master-data-charges].[Last Name]," _
    & " [master-data-charges].[First Name], [master-data-charges].[Business Unit]," _
    & " [master-data-charges].[Cost Center], [master-data-charges].[Amount Charged]," _
    & " [master-data-charges].[Manual Check Date]" _
    & " FROM [master-data-charges] WHERE" _
    & " [master-data-charges].[Pay date] like ([Forms]![frm_Find_User]![txt_Search])" _
    & " or [master-data-charges].[EC Member] Like ([Forms]![frm_Find_User]![txt_Search])" _
    & " or [master-data-charges].[Supervisor] Like([Forms]![frm_Find_User]![txt_Search])" _
    & " or [master-data-charges].[Last Name] Like ([Forms]![frm_Find_User]![txt_Search])" _
    & " or [master-data-charges].[First Name] Like ([Forms]![frm_Find_User]![txt_Search])" _
    & " or [master-data-charges].[Business Unit] Like ([Forms]![frm_Find_User]![txt_Search])" _
    & " or [master-data-charges].[Cost Center] Like ([Forms]![frm_Find_User]![txt_Search])" _
    & " or [master-data-charges].[Amount Charged] Like ([Forms]![frm_Find_User]![txt_Search])" _
    & " or [master-data-charges].[Manual Check Date] Like ([Forms]![frm_Find_User]![txt_Search]);"

    Don't forget to add Amount Charged and Manual Check Date to the row source of the list box:

    List_Result.RowSource = "Select [Pay date],[EC Member], [Supervisor], [Last Name],[First Name],"
    & " [Business Unit], [Cost Center], [Amount charged], [Manual Check Date] from Search_Result"

  12. #12
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table fields do not get populated (Access2002)

    With "Like" - only headers shown when performed text or numeric search.
    Thanks

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table fields do not get populated (Access2002)

    It works for me - modified database attached.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table fields do not get populated (Access2002)

    Thanks so much for bearing with me, we are almost there, I mean all works except once in 20 searches I get error messages like "Data has changed" and I know because I am changing it. LOL

    I've added Private Sub cmd_Clear_Click()
    List_Result.RowSource = ""
    txt_Search.SetFocus
    txt_Search.Text = ""

    End Sub

    I would like one button to change caption to Clear record instead of having 2 buttons, can't find in help.

    Thanks HansV for your time and help

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table fields do not get populated (Access2002)

    Clear the Record Source of the form. The form itself doesn't need to be bound to a table.

    You can change the Caption property of a button in code, but it's not clear (pun not intended) to me what you want here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •