Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003

    Unanswered: Help: Assign records to a variable

    Hi all!, this is my first post in this forum, I´m from Argentina and sorry by my poor english.

    This is the situation :
    I have a query named "QryFriends" with tree fields : [Name], [Age] and [Sex] then the result of that Qry drop 3 results .. Ex

    [Name] [Age] [Sex]
    Robert ...25.......M
    Brenda ...26.......F
    Mary .....36........F

    I need put this results in variables to make the body of message to send via Outlook (I have the procedure to send mails from Access via Ol)

    the body would be :

    strMSG = "Record " & (here a variable with number 1, firt record)&vbcrlf
    strMSG = strMSG & "Name : " & (variable with name Roberto) & vbcrlf
    strMSG = strMSG & "Age : " & (variable with age 25) & vbcrlf
    strMSG = strMSG & "Sex : " & (variable with Sex M) & vbcrlf
    strMSG = strMSG & vbcrlf
    strMSG = "Record " & (here a variable with number 2, second record)&vbcrlf
    and so on to the las record

    Somebody can help me how to construct the VBA code for my need?

    Thanks in advance.

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    here is a function that returns the string that you are looking for without setting up lots of vars.

    private function makeMail() as string
    	'if success, function returns the string you asked for in your post, ELSE ""
    	on error goto err_makeMail
    	dim dabs as DAO.database
    	dim rex as DAO.recordset
    	dim mailText as string
    	dim aCounter as long
    	set dabs = currentdb
    	set rex = dabs.openrecordset("QryFriends")
    	with rex
    		aCounter = 1
    		while not .EOF		'loop through all records
    			mailText = aCounter & vbcrlf & !Name & vbcrlf & !Age & vbcrlf
    			mailText = mailtext & !Sex & vbcrlf & vbcrlf
    			aCounter = aCounter + 1	
    	end with
    	makeMail = mailText		'success return
    	set rex = nothing		'tidy up
    	set dabs = nothing
    	exit function			'and quit
    	makeMail = ""			'failure return
    	msgbox err.description
    	resume exit_makeMail
    end function
    if you prefer to mess wiith vars anyway, once you are inside the
    with rex........end with
    you can have
    anyDimmedVar = !anyFieldName


  3. #3
    Join Date
    Jun 2003
    Yes!, this is that I need, is a great routine.
    I really appreciate your help, very very thanks for help me.


    Last edited by Drebin; 06-21-03 at 10:05.

Posting Permissions

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