Help creating a VBA recordset, Pulling the data I need into it, and placing them in VBA variables.

[Phred]

Backend Sql Server 2008, front end access 2007.

I have a page of VBA code which creates a new record using a SQL Insert Into statement writing to the underlying dbo_PropertyYearDetail table. The Sql query works fine. But now I need to add 7 fields from another SQL table called dbo_FeeScheduleLookup table. I then need to pull the 7 values from the Lookup table into my VBA page as 7 variables. Then I can edit them into the SQL Insert Into statement and append everything at one time.



I think this can be achieved by creating a recordset in VBA and pulling the field values from the Lookup Table into variables. The only part I need help with is creating the recordset to pull the 7 fields and assign them to variables. The 7 fields contain percentages if that makes any difference.

I've read everything I can find and I'm just not getting it. Can someone show me some sample code that might work?

Thanks

Fred

[Perceptus]

YOu can use Alter Table to add the columns in a table.

You could pull the related data for a variable using dlookup for one variable. Sample code

dim strVar as string
strVar=nz(dlookup("fieldname","tablename", "[fieldapples]='apples'"),"")

[Phred]

Hi and thanks. Two questions:

I understand the strVar=nz(dlookup("fieldname","tablename", (But I don't understand the apples part)--> "[fieldapples]='apples'"),"") What is this part doing?

Also as it is writing to a SQL 2008 table with Field Type = Decimal (2.2) Percentage should I have the variable dimmed as Long instead of string?

I think the dlookup is the best route.

I don't understand how Alter Table works but I will look it up.

Thanks I appreciate your help.

Fred

[Phred]

I got it, thanks. Appreciate the help. I will mark this closed.

Fred