Macros and Programming


CardboxForumsMacros and Programming > "Using contents of fields as look-up table for entry into other fields"

Using contents of fields as look-up table for entry into other fields

Posted By Post

Chris H

3-Jan-2014 09:54

I wish to use the contents of fields EE01 to EE20 of database "A" to look up records in a separate database "B" (fields name SYMBOL)(or use Key/Item DIM objDictionary)and extract the contents of (field name ATWeight) from "B" (or the Item from objDictionary)then place the results into Field A01 to A20 of "A" respectively.
It is the process of 'extracting and converting' the data in fields "A"-EE to a search entry into "B"-Symbol and then taking the data from the selected record "B"-ATWeight and placing it into "A"-A01. (Then to iterate 20 times which I can work out for myself.)I have tried using various examples in the Cardbox book but cannot get them to work for me.
Has anyone the patience to explain this in words of one syllable? I have never had any formal lessons in any kind of programming / macro writing but have written several less demanding ones by trial and many errors.


3-Jan-2014 11:12

I think I can help you the best by a simple example. You can also write beautiful interesting macros for this. But I think the best is to start writing macros as easy as possible.

I hope I understand your 'process' well.
I understood:
- Content of current record of DB A, content of fields EE01-EE20, are used to select a record in DB B in field SYMBOL01-symbol20.
- In DB B, the selected record (it is always one...?!), you want to pick up fields ATWeight01 - ATWeight20 and place the content of it into DB A, in the record where you started, fields A01-A20.

This is a way:

First: both db's are opened.
DB A is our starting point.
First read content in a variable of current record of db a, field EE01-EE20:
-ee01 = Fields("EE01")
-ee02 = Fields("EE02")
-and so on.
Then Activate database B.
Activate "B"

We always start (of course) at level 0 in DB B. So:
SelectionLevel = 0

Then the selection process. I suppose there were no empty Fields in db A.:
ActiveWindow.Select Fields("SYMBOL01"),ee01
ActiveWindow.Select Fields("SYMBOL02"),ee02
and so on.

Cardbox will raise an error message when there are on a certain level no records left to select (There is then something wrong with you data in db B I think).
There will also an error if on of the ee## fields is empty.
If both can occur, there are extra statements needed to avoid these error messages.

The result will be 1 record (I hope). However, to be sure there is one record selected: we check first and let Cardbox pause the macro when there is more then 1 record left in your selection in db B. You can decide what to do then.
If Records.Count > 1 then Pause "There are " & Records.Count & " records selected. Something wrong?",300,300.

The pick up the data in B:
ATW01 = Fields("ATWeight01")
ATW02 = Fields("ATWeight02")
ATW03 = Fields("ATWeight03")

Then go back to db A:
Activate "A"
Place the found data into the record:
Fields("A01") = ATW01
Fields("A02") = ATW02
Fields("A03") = ATW03

Something like this can do it. It can be done much more sophisticated. However, that is not the way I think I can help you the best now.

I hope this will help you.

Chris H

17-Jan-2014 08:02

Helo Bert,

Many thanks for your patience in outlining a suitable solution to my long and rambling question.
I will 'convert' this into Carbox speak within the next day or two.
Once again
Many Thanks


24-Feb-2014 07:36

please email me and I am willing to pay you to write a short macro for me.
I have an Excel workbook with 24 sheets of data. The first sheet contains data in Column A that is bad data that needs to be deleted from all the other worksheets.

I will call the first worksheet with the bad data (1500 rows) "bad data"

Would like to record a macro that looks at data in A1 cell of "bad data" worksheet, finds matching data in all other worksheets, deletes that entire row of data, then also deletes the bad data in the original worksheet "bad data".

After delete row A in "bad data" worksheet, the bad data that WAS in A2 moves up to A1. would like to run the macro again using the new bad data that has been moved up to A1 as a result of previous deletions.

Or run a macro that looks up all data from column A in the first worksheet (1500 rows) and deletes all rows with matching data in the other 24 sheets.

The data is bad emails that i am deleting from my .csv email database file.

© 2010 Cardbox Software Limited   Home