Access Database

[Paluccie]

Ok

I'm using Microsoft Access for my customer database. I have a table that has a list of serial numbers that I will need to input into a text box. I have the form created already. Now, what I want to do is create a button where it will take a number from the serialnumbers table and input it into the text box, but also remove it from the serialnumbers table. If anyone knows how to do this, i'm open for all suggestions. Thanks in advance.


Table with serial numbers is called
serialnumbers

Text box that i'm using is
SerialNumber

Thanks again!

[vass0922]

ok to clarify
You have a table of (probably) unique serial number

You want to
Push button
-- Fetch S/N from S/N table
-- Put S/N into text field on form
-- Delete given S/N from table of available S/N's?

If so best method would be to create a quick macro
I don't have access available at the moment, so maybe poke around google for retrieving recordsets from Access tables

If Access was a decent DB (we won't get started) you could chain together SQL Statements, but I don't think you can do that in access

The other option (that I can think of) would be to use seperate buttons. One for fetch, the other to delete.

[Paluccie]

yeah that's what i want to do

[ctaylor]

What I would do is probably a bit more complicated, but I will share it anyways.

CONCEPT:
Take the table of serial numbers and add a check box field so that when you use a serial number on a form, that you could check the check box in the serial number table. then, when you exit the database I would have it run a delete query where it would delete all records from the serial number table where the value stored in the check box is "-1".

FORM DESIGN:
1 - When you would open the form you already have, I would create an ONCURRENT event on your form ([frmYourForm]) that would open a second form [frmSerialNumberLookup] bound to the serial number table.

2 - This second form would have two controls on it:
[frmSerialNumberLookup]![txtSerialNumber] to show the serial number
[frmSerialNumberLookup]![cbxDelete] to mark the record as ready to be deleted


The row source for this second form should be set to something like

SELECT MIN [Serial number] FROM tblSerialNumbers WHERE cbxDelete = 0.

This second form ([frmSerialNumberLookup]) would have an ON OPEN event that would take the serial number value displayed on the second form and paste it in your form. Syntax would look something like this.

[frmYourForm]![txtSerialNumber] = [frmSerialNumberLookup]![txtSerialNumber]


[frmYourForm]![txtSerialNumber].requery


docmd.close "frmSerialNumberLookup"

3 - I would have an ON CLOSE event for [frmSerialNumberLookup] that would set the value of [frmSerialNumberLookup]![cbxDelete] equal to "-1"

Perhaps this is not the most eefficient method of doing this, but it should work for you.

[Paluccie]

Maybe if I attach the files this might help better.

[elmers]

vass0922: I think you can use sql in access. I did some nice little macros with a command somthing like sqlcommand or something for my db class.

[ctaylor]

<BRAIN DUMP>concerning the use of SQL statements with MS Access here are my immediate thoughts:

1 - What you can do in MS Access' VBA modules is define a string variable such as strSQL

You can then set strSQL equal to a SQL statement surrounded by quotation marks.

With a recordset object you can sefine the source property (rstTemp.source = strSQL) by setting it equal to the variable strSQL.

I'm not sure how helpful this is as I have never been able to do much with displaying the results of a record set.


2 - you can write queries directly with SQL language by going into SQL VIEW instead of design view


3 - you can manually set the record source property of a data control equal to an SQL statement. Programatically, you can set the record source property equal to a variable and define the variable within a VBA module based upon certain criteria you define.

4 - when using DDE, you can overcome the MS Access 255 character limitation of a SQL string be defining string variables that contain SQL clauses (such as the SELECT clause, FROM clause, WHERE clause etc). Compose your SQL string by concatonating the string variables together to create a SQL string that contains effectively contains more than 255 characters even though the explicit declaration of the SQL string (in the final step where you concatonate the clauses) contains less than 255 characters.

5 - a docmd.runSQL method also exists to run 'action queries'

<BRAIN DUMP>

[Paluccie]

Alright, I'm kind of new at all this SQL and query stuff, just trying to get somethings going. I am a hardware type of person not software. Anyway, if anyone knows the code or has documentation on how I would go about doing this, I would appreciate all the help. Everything that has been said, is kind of like in a different language, I understand what I have to do, now its just going ahead and doing it, is the problem. Any help that you can give will be appreciated.

Table that contains the serial numbers is called
table= serialnumbers

The form that will have the button that will be called
button Name= GetSerialNumber
Caption = Get Serial Number
form = ComputerInfo

Any other info, just ask! Thanks for the help, I appreciate it very much!

[pedantic]

I am a hardware type

aren't we all?

If you want to play around with stuff, learn how it works, or not!