Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-30-2018, 05:57 AM   #16
Scribtor
Newly Registered User
 
Join Date: Sep 2018
Posts: 16
Thanks: 8
Thanked 0 Times in 0 Posts
Scribtor is on a distinguished road
Talking Re: The reason I decided to meet you all

Project update:

This is the VBA I came up with after nearly two days of


Code:
Dim rs As DAO.Recordset
Dim qry, points, pp As String
Dim pts As Integer

pp = Me.Form.cbo_query_picker.Value
Set rs = CurrentDb.OpenRecordset("SELECT DateDiff('n',IIf([Arrival Date]<=(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Arrival Date]),IIf([Quitting Date]>=(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Quitting Date])) AS pts, Event_Participants.Participant, Event_Participants.[Event Name] FROM Event_Participants")
rs.MoveFirst
Do Until rs.EOF = True
    pts = Int(rs("pts").Value / 30) * 3
    qry = "update players set dkp = dkp + "
    qry = qry & Format(pts, "0")
    qry = qry & " where [player name] = '"
    qry = qry & rs("Participant") & "'"
    CurrentDb.Execute (qry)
    Debug.Print qry
    rs.MoveNext
Loop
rs.Close
I tried using to use the form control address directly, but the compiler kept blowing up in my face

This resolves the issue for now, but I would like to learn a few things from this:


As I'm pretty sure this code can be better, how and where am I to improve upon it?
Although it satisfied the needs of this project, I know this can be better written.

Scribtor is offline   Reply With Quote
Old 10-01-2018, 12:44 PM   #17
Scribtor
Newly Registered User
 
Join Date: Sep 2018
Posts: 16
Thanks: 8
Thanked 0 Times in 0 Posts
Scribtor is on a distinguished road
Talking Re: The reason I decided to meet you all

Dim rs As DAO.Recordset
Dim qry, points, pp As String
Dim pts As Integer

pp = Me.Form.cbo_query_picker.Value
Set rs = CurrentDb.OpenRecordset("SELECT DateDiff('n',IIf([Arrival Date]<=(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Arrival Date]),IIf([Quitting Date]>=(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Quitting Date])) AS pts, Event_Participants.Participant, Event_Participants.[Event Name] FROM Event_Participants")
rs.MoveFirst
Do Until rs.EOF = True
pts = Int(rs("pts").Value / 30) * 3
qry = "update customers set points = points + "
qry = qry & Format(pts, "0")
qry = qry & " where [Customer name] = '"
qry = qry & rs("Participant") & "'"
CurrentDb.Execute (qry)
Debug.Print qry
rs.MoveNext
Loop
rs.Close

This is the code I used to eventually scramble what I wanted from access to do, but here's my final question, when the project is complete and deployed, working properly:

Is there any way to improve upon this code, in any way? There must be a way so I don't have to assign 4 times what qry is, among other things
Scribtor is offline   Reply With Quote
Old 10-01-2018, 12:53 PM   #18
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,293
Thanks: 105
Thanked 2,492 Times in 2,288 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: The reason I decided to meet you all

Post 16 was moderated. Posting here to trigger email notifications


__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Tags
access 2013 , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Let number of characters be decided by size of text box LearningStill Forms 10 11-18-2016 08:27 AM
Nice to meet you all Rob Bombadil Introduce Yourself 0 02-04-2014 07:50 AM
Meet Deano dukeofshoe Introduce Yourself 1 10-25-2012 08:04 AM
Hello, nice to meet you :) Tracy284 Introduce Yourself 0 09-03-2012 05:31 PM
I have decided Nicolette Forms 9 08-04-2010 11:20 PM




All times are GMT -8. The time now is 09:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World