Automatically Removing Certain Numbers in Text Box (1 Viewer)

[Scooterbug]

I have two tables: Projects and Inventory

Both tables have a single bound form: Project Details and Inventory Details

Based on your post, you have what is called a One (Project) to Many (Inventory) Relationship. In that situation, you should be using a Form bound to Projects and a subform bound to Inventory. The subform should be linked via the ProjectID field.

When I place text in Text Box "Project Name" in the Projects Details form, I need that field to copy, paste, and save information to Text Box "Project Inventory" in the Inventory Details form. This information needs to be saved in both tables.

Saving duplicate data in multiple tables is a No-No. What you should be doing is storing the ProjectID of the project in the Inventory table. With a correctly setup form/subform situation, you can have the ProjectID automatically saved with each new Inventory item added.

I use Project Details (Projects) to collect information about a project via the Data Collection emails. Inventory Details (Inventory), is where I record and continually track the inventory for that project. Having the name of the project copied and pasted automatically from the two text fields would be great.

I would send you my DB but it wont let me convert it down so you can see it as I'm sure this would probably make more sense.

I have been reading a lot here and I see that many people are doing it but I don't understand their methods yet.

Could you help?

Brian

Give me a bit and I can do up a quick little db to illustrate the Form/Subform concept for you if you would like.

 

[unluckytoe]

That would be fantastic! Thank you.

 

[Scooterbug]

Here ya go. Simple little db that illustrates a form/subform. Check out Form1. The main form is the Project. There is a text box, a combo box and a command button as well. You can use that to add Inventory Items to the Project. I am assuming that you have a table that stores the ProductID and such for what you have for inventory. Add the quantity to the text box, choose the product and click the add button.

The code behind the Add button is a simple append query that adds the ProjectID, InventoryID and quantity to a table.

 

[unluckytoe]

Scooterbug:

That's awesome, it's much easier to learn looking at it than reading about it (for me that is)! I implemented it into my DB and I'm thrilled with the results!

I have been searching all throughout to create a time tracking "system" in my DB. I need to track how long it takes to turnaround a project (Days, Hours, Mins), and the average time it takes to turn around each device in that project. Could you point me in a direction that will help me?

Again, thank you so much for all your help.

Brian

 

[Scooterbug]

Scooterbug:

That's awesome, it's much easier to learn looking at it than reading about it (for me that is)! I implemented it into my DB and I'm thrilled with the results!

I'm the same way...I learn much better by actually seeing something rather than having it explained to me.

I have been searching all throughout to create a time tracking "system" in my DB. I need to track how long it takes to turnaround a project (Days, Hours, Mins), and the average time it takes to turn around each device in that project. Could you point me in a direction that will help me?

Again, thank you so much for all your help.

Brian

All you need to do is to record the StartTime and StopTime of the Project and Device. By using Now(), it will record the date and time. So, when a project is created, you can have the db record the StartTime. Same with a device. However if it is going to be possible to start and stop working on a device, you will need to record that data into a separate table. Calculating the difference is simple after that. The DateDiff function (To read about DateDiff, open up the VBA editor then help and search for it) can be used to calculate the difference between two dates and/or Time.

 

[unluckytoe]

Scooterbug:

This is awesome! Thank you so much, everything is working as planned.

After searching through this forum, I couldn't quite find the answers that I was looking for. I have split my DB and the back end is on our company shared drive and the front end is being used by two people. It's really slow. I was reading about the "open database" method but I haven't a clue on how to set it up. From what I have read it will help the performance.

What's your take? How can I set this up if it will help me.

Thank you

Brian

 

[Scooterbug]

After searching through this forum, I couldn't quite find the answers that I was looking for. I have split my DB and the back end is on our company shared drive and the front end is being used by two people.

Each person should have a local copy of the front end on their own computer. If they both are using the same front end over the network, that could explain the slow down. Having two ppl use the same front end could also lead to corruption. Plus, the advantage of each person having their own copy is that if one person's front end gets corrupted for whatever reason, then the other users wont be down. Just make sure you keep a clean copy somewhere.

I was reading about the "open database" method but I haven't a clue on how to set it up. From what I have read it will help the performance.

What's your take? How can I set this up if it will help me.

Not exactly sure what the "open Database" method you are referring to, so cant comment on it. Can you explain it a bit?

 

[unluckytoe]

Scooterbug:

Sure, I'm sorry that I didn't explain further. Here's the link that I found when looking up "performance for access 2007" in google.

http://www.fmsinc.com/MicrosoftAccess/Performance/LinkedDatabase.html

It states at the bottom that there should be noticable performance gains. But by looking at it, I have no idea how to place it in my DB.

Thank You

Brian

 

[Scooterbug]

To impliment the code, you need to create a module with the code provided on the page. You would substitue this line:

 strName = "H:\Dir\Backend1.mdb"

With the path to your backend database. The code provides for serveral backends. If so, just replace the directory of each backend. If it's more than 2, just add more case statements.

Then, on the first form that opens, you put the following code:

OpenAllDatabases True (I would put this on the On Load event)

then use OpenAllDataBases False when you close the database.

Honestly, I'm not sure how much that helps. Our main database here at work has over 60 tables and at least as many Forms and Queries. I store the data in several different backends and performance isn't that bad. I have at least 6 ppl in the db at all times.

Make sure each user has their own copy of the front end on their local drive and see if that helps with the performance.

 

[unluckytoe]

Scooterbug:

Well you're right. I tried it but I didn't notice any difference. It's OK, I learned something new.

Something else, I have been looking around everywhere because I don't like asking so many questions, but I can't find this solution.

One thing I would like to add to my application is a double click feature that when the tracking numbers on my DB are double clicked, it will open up IE and automatically go to UPS and place the tracking number in their search box. I'm pretty sure that is going to be way to complicated.

Again, thank you for everything!

Brian

 

[Scooterbug]

One thing I would like to add to my application is a double click feature that when the tracking numbers on my DB are double clicked, it will open up IE and automatically go to UPS and place the tracking number in their search box. I'm pretty sure that is going to be way to complicated.

Thats a bit out of my league...never had to do it, so never really looked into it. We do our shipping via USPS and I get delivery confirmation reports so I import them. You might want to possibly enter a tracking number and note the web address. It it look like there is a set format for the address, you could copy that and use code to plug in the tracking number. You can use the VBA command FollowHyperlink to open up that specific webpage (Search VBA Help, keywork followhyperlink for more details on the usage)

 

[unluckytoe]

Scooterbug:

I'm so excited! Thanks to your help I figured it out and I'm am very pleased! I thank you for taking your time to help people like me in the whole learning process.

Another one for you...I have searched here but need more details. I read as much as I could here about normalization, and I have a question regarding Checkboxes.

I have a form with a few text fields for vital information needed. I also have about 10 Checkboxes that are for QC (Quality Control) attached to the same form. I have the text fields and QC checkboxes all in the same table. From what I have been reading, this is not normalized. How would I go about creating a new table and linking the checkboxes to that table? Additionally, not all checkboxes are ticked at any given time for each device, so the main table will need to keep record of it.

Hopefully this makes sense.

Thank you again:

Brian

 

[Scooterbug]

I have a form with a few text fields for vital information needed. I also have about 10 Checkboxes that are for QC (Quality Control) attached to the same form. I have the text fields and QC checkboxes all in the same table. From what I have been reading, this is not normalized. How would I go about creating a new table and linking the checkboxes to that table? Additionally, not all checkboxes are ticked at any given time for each device, so the main table will need to keep record of it.

Hopefully this makes sense.

Makes perfect sense. Here is how I would do it:

tblQualityControlSteps
QCid (PK, AutoNumber)
QCdescription

tblQCdata
QCdataID (PK, AutoNumber)
OrderID (Order or whatever is being QCed, FK)
QCid (FK)
Passed (Yes/No)

Enter the data for each QC step into the first table. You can then use a combo box with that table as the row source. Have a button for Pass and one for Fail on the QC form. Then, use an Insert sql statement in code to append the data to the table. ie:

dim sInsertSql as string
 
sInsertSql = "INSERT INTO tblQcData " & _
                                   "(OrderID, QCid, Passed) " & _
                "VALUES " & _
                                   "(" & me.OrderID & ", " & _
                                         me.ComboBox & ", " & _
                                          "YES" & ")"
currentdb.execute sInsertSql

You could even use a list box to display the QC steps and if it's a pass/fail. Requery the list box after the currentdb.execute command

 

[unluckytoe]

Scooterbug:

This kinda makes sense, would it be to much of a bother to maybe build a DB for me to see?

I'm still going to try it like described and cross my fingers.

Thank you so much:

Brian

 

[Scooterbug]

I still have the previous example I made for you...so I added the Pass/Fail to it. I am assuming you want to test the Inventory Items from each Project, so I built upon that.

Form 2 uses a combo box to select the project. The Inventory Items from that project display in the first list box. Click on one, slect the QC Step from the combo box to the right. Click on either Pass or Fail. The step and the result will display in the bottom list box for whatever item is selected in the top list box. Any questions, let me know.

 

[unluckytoe]

Scooterbug:

Works like a charm...you're awesome.

Question for you...how would I go about using 1 combobox (has to be bound) to filter a second (has to be bound).

ie Combo A = Customers

Combo B = End Users

I need the Customer's combo to filter the End Users combo. I have a query that combines the two I just don't know how/if you can filter this way.

Thank you again:

Brian

 

[Scooterbug]

What you are looking for is called Cascading Combo Boxes. The row source is built in code and assigned when the first combo box is updated. For example:

ComboBoxA After_Update
 
dim sComboRS as string
 
sComboRs = "SELECT tblEndUsers.* " & _
                 "FROM tblEndUsers & _
                 "WHERE EndUserID = " & _
                 me.ComboBoxA.Column(3)
 
me.ComboBoxB.rowsource = sComboRs
me.ComboBoxB.Requery

 

[KTuohy]

Hi, I've followed this thread and am hoping you can answer for me: what if the data in your field is a different length, but you're always looking for the same string?
ex: 800-1838-01EX or 68-424-01EX (but the tail end will always be EX). I only want the what comes before the EX or a %Q or something else.

I would appreciate any help from y'all. Thank you very much.

 

[boblarson]

If it was always the end, in a query you can use

MyNewFieldName:Left([FieldName],Len([FieldName])-2)

 

[KTuohy]

thank you, saved me a nest of trouble with IIFs.