Table fields do not get populated (Access2002)

Topic

New Reply

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.

Reply | Quote

Replies

Edited by HansV to present data in table form – see post 164109

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

Pay date	EC Member	Supervisor	Last Name	First Name	Bunit	Cost Center	Amount Charged	Manual Check Date

Reply | Quote

Edited by HansV to present data in table form – see post 164109

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

Pay date	EC Member	Supervisor	Last Name	First Name	Bunit	Cost Center	Amount Charged	Manual Check Date

Reply | Quote

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

Reply | Quote

I don’t think we can solve that without seeing the database. See post 401925 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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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”

Reply | Quote

With “Like” – only headers shown when performed text or numeric search.
Thanks

Reply | Quote

It works for me – modified database attached.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

With “Like” – only headers shown when performed text or numeric search.
Thanks

Reply | Quote

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”

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I don’t think we can solve that without seeing the database. See post 401925 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!

Reply | Quote

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

Reply | Quote