Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    Argentina
    Posts
    2

    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
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here is a function that returns the string that you are looking for without setting up lots of vars.

    Code:
    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
    		.movefirst
    
    		aCounter = 1
    
    		while not .EOF		'loop through all records
    			mailText = aCounter & vbcrlf & !Name & vbcrlf & !Age & vbcrlf
    			mailText = mailtext & !Sex & vbcrlf & vbcrlf
    			.movenext
    			aCounter = aCounter + 1	
    		loop
    	end with
    
    	makeMail = mailText		'success return
    
    exit_makeMail:
    	set rex = nothing		'tidy up
    	set dabs = nothing
    	exit function			'and quit
    
    err_makeMail:
    	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

    izy

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


    bye

    Drebin.
    Last edited by Drebin; 06-21-03 at 11: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
  •