[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Pull ID from another form..autonumber

Posted on 2000-01-05
12
Medium Priority
?
289 Views
Last Modified: 2010-05-02
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.

0
Comment
Question by:KathyBrowning
  • 6
  • 6
12 Comments
 
LVL 1

Accepted Solution

by:
jamauss earned 450 total points
ID: 2326618
Kathy,
   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.

-Jason
0
 

Author Comment

by:KathyBrowning
ID: 2326946
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?

Kathy
0
 
LVL 1

Expert Comment

by:jamauss
ID: 2327044
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.

Jason
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:KathyBrowning
ID: 2327077
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.  
0
 

Author Comment

by:KathyBrowning
ID: 2327083
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.  
0
 
LVL 1

Expert Comment

by:jamauss
ID: 2327492
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.

Jason
0
 

Author Comment

by:KathyBrowning
ID: 2327852
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.
0
 
LVL 1

Expert Comment

by:jamauss
ID: 2327974
Kathy,
   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
   rstMyData.MoveFirst
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.

Jason

0
 

Author Comment

by:KathyBrowning
ID: 2333051
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

then
/// 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.addnew
   rs2!contractorID = vContractorID
   rs.update
End Sub


0
 
LVL 1

Expert Comment

by:jamauss
ID: 2333139
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.Edit
rs2!contractorID = vContractorID
rs2!Update

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.
0
 
LVL 1

Expert Comment

by:jamauss
ID: 2333140
Sorry, in that last example it should be rs2.Update, not rs2!Update.
0
 

Author Comment

by:KathyBrowning
ID: 2333613
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question