UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Fetch Data From Query To Textbox Using Grouping, Access 2016    
 
   
prithwi
post May 17 2018, 02:40 AM
Post#1



Posts: 21
Joined: 24-April 18



Please check the attachments. Need to send data to textbox from query tables grouping by class.

Attached File(s)
Attached File  Textbox.JPG ( 20.08K )Number of downloads: 0
Attached File  Query.JPG ( 37.81K )Number of downloads: 9
 
Go to the top of the page
 
pere_de_chipstic...
post May 17 2018, 03:39 AM
Post#2


UtterAccess Editor
Posts: 10,220
Joined: 8-November 07
From: South coast, England


Hi prithwi

Could you give more detail?

Does the data need to be displayed on a form in a text box - does it need to be saved (not normally advisable) or do you need it in a report?

If in a report then you can use the report's sorting and grouping function, if a form then you can use the grouping function in the query to provided the data points you need.

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
prithwi
post May 17 2018, 03:52 AM
Post#3



Posts: 21
Joined: 24-April 18



i will create a form on which there will be the text box. A command button will be required to fetch the data from the query as result in the text box on hourly or timely basis. in the provided format. Again after 1 hour or more if clicked on the text box the old data will not be shown and the new data that is entered will be shown in the text box.

Please help.
Go to the top of the page
 
pere_de_chipstic...
post May 17 2018, 04:18 AM
Post#4


UtterAccess Editor
Posts: 10,220
Joined: 8-November 07
From: South coast, England


Hi Prithwi

you could try the following code behind your command button
CODE
Public Sub btnShowData_Click()
On Error GoTo err_proc
Dim strSQL As String
Dim rst As DAO.Recordset
Dim dtDate As Date
Dim strText As String
Dim strClass As String

dtDate = Me.SelectedDate

strSQL = "SELECT Entry_Date, Class, Batch_No, Tensile_Strength, Elongation, Brinell_Hardness FROM YourTable WHERE Entry_Date = #" & Format(dtDate, "mm/dd/yyyy") & "# SORT BY CLass;"
Set rst = CurrentDb.OpenRecordset(strSQL, , dbOpenSnapshot)
While Not rst.EOF
If Not strClass = rst!Class Then
strClass = rst!Class
strText = strText & vbCrLf & strClass
End If
strText = strText & vbCrLf & rst!Batch_No & "," & rst!Tensile_Strength & "," & rst!Elongation & "," & rst!Brinell_Hardness
rst.MoveNext
Wend
If Not strText = "" Then
strText = "Mechanical Report" & vbCrLf & "Date: " & dtDate & vbCrLf & strText
End If
Me.YourControlName.Caption = strText

exit_proc:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub

err_proc:
MsgBox Err.Description
Resume exit_proc

End Sub
(Air Code - Not Tested)

I've assumed that you will use a label control rather than a text box, if you use a text box you will need to remove the '.Caption' from 'Me.YourControlName.Caption = strText'. You may also need to replace each occurrence of vbCrLf with Chr(13).
You must also replace the text in blue with the details appropriate to your database.

hth
This post has been edited by pere_de_chipstick: May 17 2018, 04:22 AM
Reason for edit: Moved last command in code

--------------------
Warm regards
Bernie
Go to the top of the page
 
prithwi
post May 18 2018, 06:03 AM
Post#5



Posts: 21
Joined: 24-April 18



what is me.selecteddate

i am using text8 and command7 as text box and command button and query1 as the structure

error received as:

Syntax error (missing operator) in query expression 'Entry_Date= #12/301899# SORT by Class'.

what to do??
Go to the top of the page
 
pere_de_chipstic...
post May 18 2018, 06:25 AM
Post#6


UtterAccess Editor
Posts: 10,220
Joined: 8-November 07
From: South coast, England


Hi prithwi

"What is Me.selecteddate"
I have assumed a control (named "selecteddate") on your form by which you enter the date of the records you want to display.

"Syntax error (missing operator) in query expression 'Entry_Date= #12/301899# SORT by Class'."
My error blush.gif !, "SORT BY" should be "ORDER BY"

The error message entry date should be 12/30/1899, which indicates that no date is entered (dtDate = 0),

If you only wanted to display entries for the current date then replace
dtDate = Me.SelectedDate
by
dtDate = Date()

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
pere_de_chipstic...
post May 18 2018, 06:33 AM
Post#7


UtterAccess Editor
Posts: 10,220
Joined: 8-November 07
From: South coast, England


P.S.

"i am using text8 and command7 as text box and command button and query1 as the structure"

You will need to replace the line "Me.YourControlName.Caption = strText" with (e.g.) "Me.text8 = strText", as suggested in my earlier post, however it would be better to use more meaningful names for your controls and query as this makes it easier to understand and debug, both now and in the future if you ever have to update your code.

In my code I suggested using a recordset based on an SQL string rather than a query as this allows you to define the records date period you wish to display; this can still be achieved using a query however you need to be able to specify the query parameters to return only the records you need.

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
prithwi
post May 18 2018, 08:07 AM
Post#8



Posts: 21
Joined: 24-April 18



Hi.

Thanks a lot. Will check and confirm you.

Thanks again..
Go to the top of the page
 
prithwi
post May 18 2018, 08:21 AM
Post#9



Posts: 21
Joined: 24-April 18



Hi,

What sort of control do you prefer to add in the form to select the date?

ps
Go to the top of the page
 
pere_de_chipstic...
post May 18 2018, 08:52 AM
Post#10


UtterAccess Editor
Posts: 10,220
Joined: 8-November 07
From: South coast, England


Hi prithwi

"What sort of control do you prefer to add in the form to select the date?"
In this instance, I would probably use a 1 column combo box with the row source returning the available dates eg.

SELECT Entry_Date FROM YourTable Group BY Entry_Date ORDER BY Entry_Date Desc;

--------------------
Warm regards
Bernie
Go to the top of the page
 
prithwi
post May 18 2018, 11:55 PM
Post#11



Posts: 21
Joined: 24-April 18



Hi,

tHANKS A lOT. Great Job..

Sending you the screenshot of the form after doing as you said.
Attached File(s)
Attached File  Complete.JPG ( 41.99K )Number of downloads: 5
 
Go to the top of the page
 
prithwi
post May 18 2018, 11:58 PM
Post#12



Posts: 21
Joined: 24-April 18



Hi,

Can i use the same as i did for other text box because the data that is shown in the text will be forwarded as text sms to the users. Earlier i used as follows:

="QA LAB: " & [FORM_DATA] & Chr(13) & Chr(10) & " Entry Date: " & [ENTRY_DATE] & Chr(13) & Chr(10) & " Shift: " & [SHIFT] & Chr(13) & Chr(10) & " DN: " & [DN] & Chr(13) & Chr(10) & " Class: " & [Class] & Chr(13) & Chr(10) & " BN: " & [BATCH_NO] & Chr(13) & Chr(10) & " TS N/mm2: " & [TENSILE_STRENGTH] & Chr(13) & Chr(10) & " Elong: " & [ELONGATION] & Chr(13) & Chr(10) & " BH HBW: " & [BRINELL_HARDNESS] & Chr(13) & Chr(10) & " Chn Spd: " & [CHAIN_SPEED] & Chr(13) & Chr(10) & " Remarks: " & [REMARKS] & Chr(13) & Chr(10)

I got it in new lines and with spacing.

Can u just expedite and provide the same in VBA for the text box as in attachment.

Thanks
Go to the top of the page
 
pere_de_chipstic...
post May 19 2018, 02:34 AM
Post#13


UtterAccess Editor
Posts: 10,220
Joined: 8-November 07
From: South coast, England


Hi prithwi

Not sure I understand your question. Could you provide more details

In essence, you should be able to use the same, or similar, code to create a text string which can then be used as the text for your SMS message.

--------------------
Warm regards
Bernie
Go to the top of the page
 
prithwi
post May 23 2018, 02:45 AM
Post#14



Posts: 21
Joined: 24-April 18



Hi

when i am using that format in a text box to send message it is showing the message in the cell as per data fields one by one. When I am showing according to the data that is grouped by DN or class it is not show sequentially as per grouping. all coming in a single line.

Please provide a format to send the data sms as per grouping.

Thanks
Go to the top of the page
 
pere_de_chipstic...
post May 23 2018, 03:30 AM
Post#15


UtterAccess Editor
Posts: 10,220
Joined: 8-November 07
From: South coast, England


Hi prithwi

You could try replacing " & Chr(13) & Chr(10) " by " & vbCrLf & " in the SQL string for the SMS text.

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
prithwi
post May 23 2018, 04:01 AM
Post#16



Posts: 21
Joined: 24-April 18



I am attaching the screenshot of the sms received and data send.

Please kindly check.
Attached File(s)
Attached File  SMS.JPG ( 26.35K )Number of downloads: 0
Attached File  Datasend.JPG ( 15.15K )Number of downloads: 0
 
Go to the top of the page
 
pere_de_chipstic...
post May 23 2018, 04:24 AM
Post#17


UtterAccess Editor
Posts: 10,220
Joined: 8-November 07
From: South coast, England


Hi prithwi

Can you post the SQL string that you are using to create the sms text?

--------------------
Warm regards
Bernie
Go to the top of the page
 
prithwi
post May 23 2018, 07:51 AM
Post#18



Posts: 21
Joined: 24-April 18



hI.. Sending you the full code:

Option Compare Database

Private Sub Command10_Click()

Dim strURL As String
Dim neMb As Variant
Dim msGe As String

Set appIE = CreateObject("InternetExplorer.Application")
neMb = Me.Number.Value
msGe = Me.Text8.Value


'strURL = "http://bhashsms.com/api/sendmsg.php?user=success&pass=654321&sender=BSHSMS&phone=" & neMb & "text=" & msGe & "priority=ndnd&stype = normal"

With appIE
.Navigate strURL
' uncomment the line below if you want to watch the code execute, or for debugging
'.Visible = True
End With
' loop until the page finishes loading
Do While appIE.busy
DoEvents
Loop

Set appIE = Nothing
'SET appIE.Quit




End Sub

Private Sub Command7_Click()

On Error GoTo err_proc
Dim strSQL As String
Dim rst As DAO.Recordset
Dim dtDate As Date
Dim strText As String
Dim strDN As String


dtDate = Me.selecteddate

strSQL = "SELECT Entry_Date, DN, Class, Batch_No, Tensile_Strength, Elongation, Brinell_Hardness FROM MECH_TAB WHERE Entry_Date = #" & Format(dtDate, "mm/dd/yyyy") & "# Order BY DN;"
Set rst = CurrentDb.OpenRecordset(strSQL, , dbOpenSnapshot)
While Not rst.EOF
If Not strDN = rst!DN Then
strDN = rst!DN
strText = strText & vbCrLf & strDN
End If
strText = strText & vbCrLf & rst!CLASS & "," & rst!BATCH_NO & "," & rst!TENSILE_STRENGTH & "," & rst!ELONGATION & "," & rst!BRINELL_HARDNESS
rst.MoveNext
Wend
If Not strText = "" Then
strText = " QA LAB Mechanical Report: " & Chr(13) & " Date: " & dtDate & strText
End If
Me.Text8 = strText

exit_proc:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub

err_proc:
MsgBox Err.Description
Resume exit_proc

End Sub

Private Sub List126_Exit(Cancel As Integer)
Dim frm As Form, ctl As Control
Dim varItm As Variant, intI As Integer

Set frm = Forms!MECH_ALL_SMS
Set ctl = frm!List126

Number = "" 'just add in this line in the VBA

For Each varItm In ctl.ItemsSelected
Number = Number & LTrim(RTrim(ctl.Column(1, varItm))) & ","
Next varItm
Number = Left(Number, Len(Number) - 1)
End Sub


Go to the top of the page
 
prithwi
post May 24 2018, 12:57 AM
Post#19



Posts: 21
Joined: 24-April 18



Hi,

Any Update??
Go to the top of the page
 
pere_de_chipstic...
post May 24 2018, 12:17 PM
Post#20


UtterAccess Editor
Posts: 10,220
Joined: 8-November 07
From: South coast, England


Hi prithwi

I have not sent messages on SMS with Access, however I believe you will need to recreate the recordset to create the text string for the SMS message replacing each vbCrLf with Chr(13) to create 'msGe'

hth

Edit:
You may be able to use a replace function on the Text8 String to replace the vbCrLF with Chr(13)
This post has been edited by pere_de_chipstick: May 24 2018, 12:20 PM

--------------------
Warm regards
Bernie
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    26th May 2018 - 05:16 AM