New project causing some head scratching!!! 2

[Ati2ude]

I have been tasked with converting a FoxPro application to MS Access. I was able to convert the database with ease, however the person who originally buil the app did not use any kind keys or normalization to build it. So that has given me some headaches. I have built several forms and am now trying to complete several items on my TODO list.

1) Identify the current record I am on in my form. I have had to remove my navigation bar due to some updates that must take place on any change. So I have created my own toolbar to move to the first, previous, next adn last record. Can anyone point me in the direction of identifying the record I am on?

2) Also I have created a PK for the main table. This table has a realtionship to another table using a field that is a text value. I would like to modify the second table to hold the value of the PK vs. the text field. I have tried the following SQL statement, but Access does not like it for some reason.

update RELXREF set key = (select key from REL_MAST where user_num = RELXREF.user_num)

Sorry for the lengthy post but Access has its own way of doing things and I am finding I do not like it as much as I thought I did. Any pointers would be great!!

Thanks in advance,
Brian

 

[mph1]

To answer the first question, try the following.
Place this code in the On Current of the form property.
-----------------------------------------------------------
Private Sub Form_Current()
If Me.NewRecord Then
Me.lRecordXofY.Caption = "New Record (" & DCount("*", "TableName") & " existing records)"
Else
Me!lRecordXofY.Caption = "Record " & [CurrentRecord] & " of " & DCount("*", "TableName")
End If
End Sub
-----------------------------------------------------------
Then in the form create a label and in the Name property of the label use lRecordXofY

Suggestion - make sure that everything that you imported from FoxPro, tables, are normalized. I think Access is better once you get used to using. There is a learning curve; however, there are many things that can be done with Access.

Hope this helps.



An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[PHV]

For the second point, you may try something like this:
update RELXREF inner join REL_MAST on RELXREF.user_num = REL_MAST.user_num
set RELXREF.key = REL_MAST.key;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya Ati2ude . . . . .

[blue]I have been tasked with converting a FoxPro application to MS Access. I was able to convert the database with ease, however the person who originally buil the app [purple]did not use any kind keys or normalization[/purple] to build it. So that has given me some headaches. [purple]I have built several forms[/purple] and am now trying to complete several items on my TODO list.[/blue]

Have you setup your tables, normalized and set relationships?

If not, better [blue]to do[/blue] this first or your [purple]headed for a great many more headaches[/purple]. It appears as if you've jumped in feet first, since you've already constructed forms (I could be premature in this).

It appears your next [blue]to do[/blue] is seperating out data for their respective tables and how to transfer the data proper.

Once you get the data in their tables proper, you'll be free to design your forms with ease. [blue]You'll be able to have that Bud, instead of another Excedrin![/blue]

See Ya! . . . . . .

 

[willir]

Brian

You have had three top notch Tek-Tipsters respond to your post. I have to agree with AceMan on this one.

Before moving on, you would be much further ahead to create a normalized database and then use the data from the FoxPro database to populate the database.

Working with a database built by some one else, especially without documentation is a tough job. ...But if done right, you will have a much better product in the end.

Richard

 

[Ati2ude]

Thanks for all of the pointers. Here is an update of where I am.

I have completed the normalization of the database and set up my relationships. I was also able to convert the keys to new fields that I created to ensure the integrity. I began to wonder how this thing every functioned properly in Fox though. Overall I think I am in good shape and have some small loose ends to tie up. Thanks again for eveything.


Brian

 

[Ati2ude]

One last question, I hope.

I have a list box and want to limit my search to the current record that is displayed in the other controls on the form. I have a query that will return all of the items from the tables, but I am not sure how to limit it to the desired ones.

BTW Richard, the product already looks and feels 100% better. The only problem is that now I have created a monster on the other end of this. They keep asking for more. I do have control of the scope creep at this point by telling them once we get the first version out and in use we will address the new request they have. However that list keeps growing. I may have to break down and purchase a book on Access before this is over.

Brian

 

[willir]

Brian

You can limit the search on the second list / combo box by resetting the RecordSource.

You can use this approach with an "EventProcedure", typically AfterUpdate another lsit / combo box, or OnCurrent record event.

Your pharsing of your issue indicates the OnCurrent event procedure would be best.

Assumptions...
- The name of the list box is called for this example is called lstBox1.
- For this example, I am going to use products shipped by a supplier for an OrderEntry system.
-- SupplierID refers to the table tblSupplier and is also located on the form
-- tblSuppProducts refers to products provided by a supplier

You seem to have the basics, but jsut in case...
Open the form in design mode with the "Properties" window open (from the database window, select from the menu, "View" -> "Properties")

Select the list box in question. Now select the "Other" tab on the Properties window and note the name that appears in the "Name" field - this is the name used by the Access form for this "control". You can change the name to be more meaningful PROVIDED you do not have any code associated with the ListBox.

Now select the "Data" tab - important fields are
* ControlSource
- If there is an entry here, it is a "bound" control if the entry matches a field name for the underlying table
- If the entry is preceeded by an equal sign, =, the control is calcualted from another source
- If the entry is blank, then the control is considered "unbound"
* RowSource - can be various things including blank, a "value list", and SQL statement
* BoundColumn - if using such things as SQL statement as the RowSource, this indicates which field / column is bound / used by the control by other parts of the form.

Now select the square box in the top left of the window where the horizontal and vertical rules meet. Properties shown in the Properties window now apply to the form in general.

Select the "Events" tab on the Properties window and select "OnCurrent". If you followed mph1's suggestion you will already have an EventProcedure. If the field is blank, then select "EventProcedure" from the down down list, and then click on the "..." button to the right. This opens up the VBA coding window.

Add the following...

Dim strSQL as String, strQ as String

strQ = Chr$(34)

strSQL = "Select ProductID, ProductCode, ProductName from tblSuppProducts Where SupplierID = " & Me.SupplierID

If Nz(Me.SupplierID, 0) > 0 Then
   Me.lstBox1.RowSource = strSQL
   Me.lstBox1.Requery
End If

'Suppose you were working with a text string instead SuppCode
'Code would be as follows...
'strSQL = "Select ProductID, ProductCode, ProductName from tblSuppProducts Where SupplierCode = " & strQ & Me.SupplCode & strQ
'If Len(Nz(Me.SuppCode, "")) > 0 Then
'   Me.lstBox1.RowSource = strSQL
'   Me.lstBox1.Requery
'End If

The difference here is that text strings have to be encapsulated with the double quote, ". I use a variable for this where many other "coders" will use a triple quote, """ & SuppCode & """, or single quote within double quotes, """ "'" & SuppCode & "'".

I hear you about scope creep and 100% better. My experience has been that with well designed systems, adding on functionality tends to be much easier than attempting the same task with a database that has not been properly normalized.

Good luck.

Richard

 

[Ati2ude]

Richard,

Can you help me out on this query?

strSQL = "SELECT CODES.C_NUM, CODES.DESC, RELXFR.KEY FROM INNER JOIN RELXFR ON CODES.C_Num = RELXFR.CODE WHERE RELXFR.KEY = " & Me.Key

I am getting the required elements from the CODES table and the RELXFR table. I have to do a comparison on the values with the value of the current record to return the proper values from the CODES table.


Brian

 

[willir]

Brian

It would helpful to have more info on field data types, especially RELXFR.KEY

If RELXFR.KEY is a text string, then...

strQ = Chr$(34)
strSQL = "SELECT CODES.C_NUM, CODES.DESC, RELXFR.KEY " _
& "FROM CODES INNER JOIN RELXFR ON CODES.C_NUM = RELXFR.CODE " _
& "WHERE RELXFR.KEY = " & strQ & Me.Key & strQ;

If you don't want to use strQ, then...
strSQL = "SELECT CODES.C_NUM, CODES.DESC, RELXFR.KEY " _
& "FROM CODES INNER JOIN RELXFR ON CODES.C_NUM = RELXFR.CODE " _
& "WHERE RELXFR.KEY = " & """ & Me.Key & """;

If RELXFR.KEY is numeric, your previous SQL statement is correct...
strSQL = "SELECT CODES.C_NUM, CODES.DESC, RELXFR.KEY " _
& "FROM INNER JOIN RELXFR ON CODES.C_Num = RELXFR.CODE " _
& "WHERE RELXFR.KEY = " & Me.Key

You can use the Query Builder to verify your SQL statement.

Does one of these solutions give you what you want? I am not sure what you specifically mean by...

I have to do a comparison on the values with the value of the current record to return the proper values from the CODES table

By the way, have you defined your relationships using the Relatioships tool (from the menu, "Tools" -> "Relationships". Add your tables. Click and drag your primary key to your foreign key. Enforcing referential integrity is a good idea.)

...and thank you for the star.

Richard