Pull ID from another form..autonumber

I have two Id's on a form.  One should be pulled from form1, and the other is an autonumber.  

How can I place the ID in a global variable and then reference it in all forms?

Is the autonumber causing my DBGrid not to add any new records.  Before having any ID fields, the Grid worked fine.  Once I added the two ID's, I can't add any new records.  I physically went into Access and forced an entry for the CustomerID (the one I want to be global).  When I went back to VB I could see that 1 record, however, I still can't create a new record through VB... If I click and type on any field, nothing happens.  When I try to resize the column, it beeps, then gives me a space to add a new record, however, when physically adding in the correct CustomerID, I still can't add a record.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

   If you declare a variable as a global variable in the declarations section of a code module then it can be used or referenced in any form at any time. Just make sure you don't have a variable with the exact same name in the declarations section of the form itself because this could cause some problems. If you don't know how to add a code module let me know. How are you displaying the id's? In textboxes or with labels or what?

With the DB grid, are you using the MS hierarchical flexgrid? Adding records to the database from VB usually requires a bit of code on your part and a connection (through written code) to the database you want to work with. If you haven't specified the "AddNew" or "Edit" property of the database before you try entering things into it that could be why it's not allowing you to enter new data.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KathyBrowningAuthor Commented:
No I don't know how to do a code module in VB.  I've written loads of modules in Access, but some help would be nice.  I'm not actually even showing the user the ID.  It's a Autonumber on Form1.  Then when they go to Form2, I want to place it in Column1 of every record created in the DBGrid (don't think I'm using a flex grid.)

So in my DBGrid I had (hidden = x)

CoID  EmpID  Name Blah  Blah  Blah
  x      x    Joe  ...  ...   ....

After not being able to add a new record, I made CoID visible and physically added the ID.  I thought I wanted to keep the above format.  Is it really that testy?

Oh ok, you're designing this in Access. I thought for some reason you were creating it in VB. Sorry. Let me go over your most recent comment and I'll try to come up with a solution for you as soon as I have some time today.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

KathyBrowningAuthor Commented:
Ok, now I'm confused.  I am designing it in VB5.0 ... tied to an Access backbone.

Well actually I'm doing both, but for this part, I'm using VB.  
KathyBrowningAuthor Commented:
Ok, now I'm confused.  I am designing it in VB5.0 ... tied to an Access backbone.

Well actually I'm doing both, but for this part, I'm using VB.  
Ok, now I'm confused too. lol. Let me know this much:

What are you (or did you) design the forms for the project in? VB or Access?
What do you mean by You have an autonumber on form1? An autonumber should only be displayed on a form if it's data-bound to an object, or for your own reference. Since you told me you had done alot of modules in Access I thought you were doing your project in Access.

Ok, in VB, to create a code module, go to Project>Add Module and then double-click on module under the New tab when the dialog box comes up. Within that code module, in the Declarations section. You should create a variable (whatever name you want to hold the ID number). Declare it like so:

Global (variable name) as (Datatype)

holding a ID number, your Datatype will probably be integer.

Now no matter what form you're in and no matter what object procedure you're writing code in , you can set that variable or fill it with whatever data you want to.

If there's anything else you want to know let me know.

KathyBrowningAuthor Commented:
My original question was...How can I place the ID in a global variable and then reference it in all forms?

I guess I still need to know how I can reference it in the DBGrid?

Also, you said....
Adding records to the database from VB usually requires a bit of code on your part and a connection (through written code) to the database you want to work with. ... if it's really detailed I'll give you 100 to 150 points total. (depending on how many times I have to communicate back and forth asking additional information)

If you haven't specified the "AddNew" or "Edit" property of the database before you try entering things into it that could be why it's not allowing you to enter new data.
I have specified AddNew and Delete

All the tables were built in Access. The ID's are AutoNumber's (generated by access).  When creating a new Customer, the Autonumber works fine.  I want to capture that into the global variable when clicking on Next Form.  Then I want to place it into the new employee record.  

The reason that I am doing this in VB is because the client needs a stand alone application so that they can pass a CD out to their clients who don't have Access, Internet, or maybe Word.  I didn't to the exe in Access because I don't have the program to create stand alone exe in Access.
   Ok. Now that I no what you need, no problem. I'll take you through some steps you need to do to be able to connect to a database and then I'll supply the code you need as well. If you already have this referenced, good job. Continue on.

First, in VB, go to the drop down menu and select Project>References. Then in the listbox find "Microsoft DAO 3.51 Object Library" and check it, and click OK. You have to reference this Object Library in order to declare a variable as a database. You'll see why this is important a little later.

Now in the Declarations Section of the code module you've created, create two variables. One will be the variable name for the database, and one will be the variable name for a "recordset" variable. So Create two variables. I.E:

Global dbsClient as Database
Global rstData as Recordset

remember: after you type "as" when typing these in, the word Database and Recordset need to appear in the little pop-up lists, otherwise you might encounter problems.

Now, I assume you are familiar with what recordsets and dynasets and snapshots of data are. If not let me know and I'll try and explain the best I can.

Now, here's the code to connect to the database(using the variable above I made up):

Set dbsClient = Workspaces(0).OpenDatabase(PathName$,False,False,Connect)

PathName$ is a string variable that holds the path of the database file, including the name of the .mdb file. SO before you execute the code to connect to the database, you need to fill the variable PathName$ with something like "C:\DatabaseFolder\mydata.mdb" for example. I used a variable so that the line of code connecting to the database wouldn't be so lengthy, that's the only reason for it.

Now, that you've established a connection to the database, you can use a little SQL along with VB to get the data you want.

So Now, fill the recordset variable with the data you want. Like in the database connecting code, I fill a variable named "SQL$" to shorten the command line of code. Here's an example using the variable I made up above and a fake table called "MyTable"

SQL$ = "Select * From MyTable"

Set rstData = dbsClient.OpenRecordset(SQL$, dbOpenDynaset)

this, using that SQL$ as the SQL command, will grab all of the information out of any table you specify in the SQL statement.

Now, to get the field (for example, your autonumber field) out of the table you want, you just have to specify to VB which field you want out of the recordset that is filled with the data from the table. Let's say your AutoNumber field is called "NumberID" and you want to display it into a label called lblID_Field.

THe code you would write would be:

lblID_Field.caption = rstMyData!NumberID

The "!" tells VB that the following is a field in that recordset that you want information from.The code right above should fill a label on any form with an autonumber from the database table.

*Note... it is also a good idea to move to the first record to make sure you don't miss any records by chance. So after the code to fill the recordset, write this code:

If rstMyData.EOF <> True Then
End If

this will put you at the beginning of the recordset full of the data from the MyTable table.

This answer is a bit lenghty so I can't really remember if I've told you everything you need to know but if you encounter any more problems just let me know, like with a comment to this question and I'll be more than happy to help you out.


KathyBrowningAuthor Commented:
This is what I have so far
Global vContractorID As Integer
Global vEmployeeID As Integer

Global db As Database
Global rs As Recordset

Set db = Workspaces(0).OpenDatabase("c:\cocc\holddata.mdb", False, Flase, Connect)
Set rs = db.OpenRecordset("Open * from Profile", dbOpenDynaset)

What I need is a way to place the variable into the global variable.

ie.  rs!Contractor = vContractorID

/// don't know how to update the ID in the column of my dbGrid, ( I think it should be before the record is created.  ... but don't know)

Sub BeforeUpdateRecordDBGrid()
   rs2!contractorID = vContractorID
End Sub

Remember, its "SELECT" not OPEN in the SQL statement. OPEN wont work

If you need to put the ID from the field into a variable just set it.

vContractorID = rs!Contractor, you've got the idea, just backwards.

If you want to update the ID, use the EDIT method, not addnew

rs2!contractorID = vContractorID

Addnew will only add a new record into the table. Edit will just edit the data within the record you're in (within the recordset)

Also, if you which ContractorID you want, tell VB in the SQL statement. Say you already KNOW the contractorID you want is 50. Then set your SQL statement in the recordset opening:

SQL$ = "Select * From Contractors Where ContractorID = 50"
Set rs = db.OpenRecordset(SQL$, dbOpenDynaset)

that's just an example. let me know if you still need help.
Sorry, in that last example it should be rs2.Update, not rs2!Update.
KathyBrowningAuthor Commented:
Thanks, I realize now that I sounded like an idiot.  I still can't get it to work on the DBGrid, so I'm just not going to worry about it.  I guess I needed to reference the Grid when it added a new record.

Oh well.

Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.