Source language: Translate to:

Creating Tables in Access

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Creating Tables in Access

Postby Wrangler » Tue Feb 19, 2008 11:25 am

I'm stumped on this one.

I have an access db named expenses. At first run, it is opened, but contains no tables.

The user then enters a table name into an input box, and the table is created and opened. Each table contains, lets say 10 different fields. Eventually there will be numerous tables.

Good to this point. I can write the table names to a file, and when the user selects one from a listbox, it opens that table.

But since dbpro field variables can't contain neobook variables inside them, how would one go about updating the display according to which table is open?
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Tue Feb 19, 2008 2:48 pm

Wrangler:

a) How about using dbpGetFieldNames ... to get the information right from the horses mouth ?

b) Ditto for Tables inside a database ... dbpGetTableNames

I use these commands in my "generic" utility AccessDBU ... which can be found here ... http://www.scriptedlogic.com/AccessDBU.htm

how would one go about updating the display according to which table is open?
... what does this "display" supposed to look like ? ... but the answer lies in the use of "double quoted brackets" ... something like ...
Code: Select all
[[thisDBName].[thisTableName].[thisFieldName]]
... where [thisDBName], [thisTableName] and [thisFieldName] are variables containing the desired elements.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Tue Feb 19, 2008 3:49 pm

The display just means the textentry boxes displaying the field data for each record. I'll have to play with your suggestion about double quoted brackets. It was the only thing I didn't think of.

Thanks.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Wrangler » Tue Feb 19, 2008 4:04 pm

The double bracket doesn't seem to work either. This is what I was using:

setvar "[tripname]" "test"
I create a table with the var [tripname] (and open it)

This in a text box:
[mytrip.[tripname].$recnum]

it won't show the record number. The field data also won't display in the textentry boxes.

I need a way to create, close and open tables, and have the data display in the same textentry boxes.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Tue Feb 19, 2008 4:58 pm

Wrangler:
This in a text box:
[mytrip.[tripname].$recnum]

it won't show the record number. The field data also won't display in the textentry boxes.
As with other "double bracketed variables" ... you need to RefreshObject the TextBox in question.

I believe that when you update [abc] ... TextBoxes etc. with [abc] as its content will be automatically refreshed ... but TextBoxes with content like [xyz[abc]3] do not.

I tried a test pub with [Favorites.[thisTable].$RecCount] in a TextBox ... Favorites being the DatabaseID of my (mdb) database ... and after the RefreshObject command, the TextBox displayed the correct number.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Tue Feb 19, 2008 5:07 pm

Wow, Gaev. Hard to believe it could be something this simple. :shock:

So if I use this:

[mytrip.[tripname].clothing] in a textentry, and created an onchange sub with a refresh for each textentry object, it should work. I'll give that a try.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Wrangler » Tue Feb 19, 2008 5:24 pm

Well, apparently that trick only works in text boxes, not textentry boxes.

Using adding a date with lgcalendar as a test:

lgaMonthCalendar "" "1"
SetVar "[mytrip.[tripname].expdate]" "![cdate]"
GoSub "refreshexpobjects"

Sub:

:refreshexpobjects
RefreshObject "expdate"
return

Nothing shows in the expdate textentry box.

Hmmm. This makes the createtable routine pretty useless, I guess.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Wrangler » Tue Feb 19, 2008 5:30 pm

Well I could be go to hell. This works so far:

[mytrip.tripname.expdate]

Later:

Nah. Don't work. It just treats it as another variable, and the database doesn't retain it.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Tue Feb 19, 2008 6:04 pm

Wrangler:

a) I had [Favorites.tblFavorites.Title] as the variable to store TextEntry contents ... and a button with code ...
Code: Select all
SetVar "[Favorites.[thisTable].Title]" "ghi"
... worked fine ... TextEntry Box was updated.

b) I suggest you assign something like ... [genericExpdate] ... to the variable associated with the TextEntry Box ... then, follow up every piece of code like ...
Code: Select all
SetVar "[mytrip.[tripname].expdate]" "![cdate]"
... with ...
Code: Select all
SetVar "[genericExpdate]" "[mytrip.[tripname].expdate]"
... ditto when user updates the TextEntry Box (i.e. TextChange routine) ... and everytime you navigate to another record ... do the opposite (in the onChange routine of the dbpOpenTable command) ...
Code: Select all
SetVar "[mytrip.[tripname].expdate]" "[genericExpdate]"
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Tue Feb 19, 2008 6:23 pm

Thanks, Gaev. That looks like it should work. I will give it a good thrashing in the morning. Of course, my project is much more complicated than a bunch of textentry boxes, so I'll have to work out how it all fits in. But I can't see a problem at first glance.

This will be used in my RV Companion title in the 'Expense Log' section. I have it set up just to enter expenses, and generate reports etc. but then I thought I would like to let the user keep expenses for EACH TRIP. Kind of help seperate them all out. Most RV'ers don't live full time in them. So each time they head out, it's a unique adventure, or 'trip'. I thought about just flagging records to keep track of which expense went with which trip, but it seemed it would be easier just to create a table for each trip. And each trip table would be closed before another one is opened, so it should not be a strain on Access.

Turned out not to be as easy as I thought. But with your help, I think we've got'er whupped. I will report back here with the results. But my brain is missing in action right now, so I'll do her in the morning......
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Tue Feb 19, 2008 7:00 pm

Wrangler:

Assuming the number of trip records are not numerous ... under a hundred per trip ? ... you might make your life easier if you used a "reserved" Table (called it CurrentTrip) as "a working table" that your program interfaces with.

Then, when they select Nevada2008 ... you erase/recreate CurrentTable ... copy all records from Nevada2008 to CurrentTrip ... and let them do their updates ... when done ... erse all records in Nevada2008 ... copy all records from CurrentTable back to Nevada2008.

A one/two second blip at start/end of process vs. all the complexities of "double substitutions".


As well, keeping all records in one Table with a field to indicate name of trip ... and then filtering appropriate records ... should also be fine ... 100 records times 20 to 50 trips ... is within the "stress level" of modern day databases/computers.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Wed Feb 20, 2008 12:23 pm

Gaev:

Farted around with this all morning, and with your many suggestions in mind, came up with a working formula.

b) I suggest you assign something like ... [genericExpdate] ... to the variable associated with the TextEntry Box ... then, follow up every piece of code like ...Code:
SetVar "[mytrip.[tripname].expdate]" "![cdate]"

... with ...Code:
SetVar "[genericExpdate]" "[mytrip.[tripname].expdate]"

... ditto when user updates the TextEntry Box (i.e. TextChange routine) ... and everytime you navigate to another record ... do the opposite (in the onChange routine of the dbpOpenTable command) ...Code:
SetVar "[mytrip.[tripname].expdate]" "[genericExpdate]"


I ended up going this way, but did the opposite of what you suggested because I couldn't get it to work that way.

Let's use clothing expense as an example: First, I renamed the textentry field (there are about 30) from [mytrip.[tripname].clothing] to [clothing].

In the textentry textchange action, I put:

SetVar "[mytrip.[tripname].clothing]" "[clothing]"
which updates the db field with the entered data.

In an onchange sub, I added:

RefreshObject "Text459"
SetVar "[clothing]" "[mytrip.[tripname].clothing]"
RefreshObject "clothing"

The textobject refresh updates the recnum and reccount.

And I added a setvar after each code used to update the db:

lgaMonthCalendar "" "1"
SetVar "[mytrip.[tripname].expdate]" "![cdate]"
SetVar "[expdate]" "![mytrip.[tripname].expdate]"

The navigation buttons need nothing but the dbpnext etc. action.

I was actually close to this before, but didn't know about the refresh trick, and also had the onchange sub backwards. I should have made the decision to add this earlier, as all the horse crap I had going was confusing me. I still need to change all the reports, tie it to other things etc. to work with it, but I see no problem with that.

It's a pain in the butt, but I'm glad it works. The create and drop tables and fields actions are powerful and it would have been a big disappointment if I couldn't get this to work. Maybe in the future Dave can have neobook do some of the setvar'in for us.

So, thanks for clearing my head with your suggestions. :wink:
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Wed Feb 20, 2008 1:29 pm

Wrangler:

Perhaps too late for this project ... but along the lines of my other suggestions to keep all records in a single table ... I did a bit more reading.

Take a look at the dbpCreateView command ... it is designed for this kind of use ... you invoke dbpQuery to select a subset of the records ... then dbpCreateView to give it a view/table name ... i.e. a virtual partition instead of physical separation like the one you have right now.

So, when user selects an "expense type", you query the corresponding records and assign it the view name of (say) "CurrentExprenses" ... and viola, the same code handles all categories of expenses.

Note that there is no dbpRemoveView command ... you just dbpDrop this view/table .... unlike with physical tables, dropping view/tables just removes the virtual mapping ... not the records in the parent table.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Wed Feb 20, 2008 1:46 pm

Good one, buddy. I forgot about that action. Ran across it in the help a couple times, but didn't dwell.

Yeah, it was too late for this a while back with this project. But I've got more features in mind for which I will try to deploy it.

Actually, this would have worked real well with the Basement Organizer. But it ain't broke, so I ain't gonna fix it. I'll save it for the next one.

Phew. I guess if it was easy, everybody would be doing it.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest