Access Help Needed Quickly!

[onceandforall]

Hello,
I am realatively new to MS Access and am attempting to set-up a database system to help keep track of my Business Deals.

I will be having questions pop-up from time to time, with hopes that someone can easily answer them.

The first few dilemas I am having are probably very easy one to solve.

I have created 4 tables and the fields in each, that I will be using. The first table is product entry table. The last field is "Listed?" This is a yes/no field. What I want to happen is when Yes is chosen, I would like the Product ID to automatically appear in the second table, "In Auction" I do not know how to do this.

Speaking of Product code, I need to know how to automatically produce one based on the State Code of purchase followed by a dash, then an ascending number for each product from that state. For example, the first 2 Colordado products would be CO-0001 and CO-0002. Then a Wisconsin Product would be WI-0001. If this isn't too hard, I would like to know how to do that.

Any help would be greatly appreciated.

By the way I am using Access 2000.

Thank you,

Mark Daniels

 

[LittleSmudge]

So tblProductEntry!Listed = "Yes" then you want tblInAuction!ProductRef = tblProductEntry!ProductId but the real question is on which record in tblInAuction does this happen? The method of determining which record in tblInAuction gets the data will greatly affect how you set the value.


To add the next product code:-

Dim strStateCode As String
Dim strLastIndex As String
Dim intIndexNo As Integer
strStateCode = Get StateCode from user somehow
strLastIndex = DMax("CodeField","tblProductEntry","CodeField Like '" & strStateCode & "*'&quot

intIndexNo = Nz(Right(strStateCode,Len(strStateCode)-3),0)
intIndexNo = intIndexNo + 1
strNewProductIndex = "strStateCode & "-" & Trim(CStr(intIndexNo))

( You'll need to add some extra data protection depending on how certain you are that strStateCode will always be entered as 2 chars by the user, etc. )



QED

G LS

 

[onceandforall]

Thank you Little Smudge,

The field name in the second table "In Auction" is the same as the field name in the "Product Information" table. I have decide to simply do a simple incremental number Product ID, instead of trying to add the state code.

This is going to sound stupid, but I cannot see where one can type in the codes. Where am I able to do this.

YOu are correct in your first paragraph, and now that you know what the Field name is in both tables, could you give me the syntax, and also where it is I type it in?

Thank you,

Mark Daniels

 

[LittleSmudge]

First off, As you 'new' to Access I give you a shortcut to make life easier .. .. Avoid 'like the plague' space characters in names of table, query, form, report, fields etc.

Second bit.
If you have two tables linked in a One-Many relationship and are tending towards having the fields the same name DON'T. Access will demand that you qualify each field EVERY time you use it. Eg. tblFirstTable!FieldName as oppposed to tblSecondTable!FieldName
Whereas If you add 'Ref' to the fieldname on the Many end you can just refer to FieldName and FieldNameRef respectivly.
This saves a LOT of typing and later, when you re-read you code you can instantly tell which is the Many end of the relationship.


By
strStateCode = Get StateCode from user somehow
I simply mean that you'd probobly have a text box control somewhere on the form that allowed the user to enter the code.
Probobly a combo box with column one { width = 0cm ( hidden )} containing the code letters and the second column containing the State by name. BoundColumn = 1


I'm not following the middle bit of your question though.

Lay out the table and field names like:-
tblTableName - info about it
FirstField - info
SecondField - info if needed
etc..

tblSecondTable - comment
FirstFldName - comments
SecondFldName
etc..

Then I think I'll be able to see that you mean.


G LS

 

[onceandforall]

OK.

TblLotInfo has several fields. The Primary key is the LotID field. The last field is called "listed?" and is a yes/no type. When the user clicks the box for "listed?" this means that he has started an auction for that lot.

TBlInAuction is used for when the lot goes to auction. I want to carry over the LotID in tblLotInfo to the "LotIDref" field in tblInAuction, but only after "listed?" field is "Yes"

Before I read your wonderful advice of naming the fields the same, the LotID field was named the same. I have now changed it to :LotIDref.

I would like to know the syntax for this function, and how and where to input this syntax. Should it be a macro? Should it be something else? How would you do it?

Once I understand this, I will be able to update the fields I need in the other tables.

Thank you for your help.

Mark Daniels

 

[LittleSmudge]

Well there is ONE more question I'd need to ask.

When the user clicks the Listed tick box and creates an new record in tblInAuction will the user then need to add other info to that record ?

I thinks this is so likely that I'll go on the assumption of YES. If it is really No then another easier solution is available.

If the tick box controlName is tikListed then in the tickBox AfterUpdate procedure put in code:-

Private Sub tikListed_AfterUpdate()
If tikListed Then ' Do this when set to true ( not when set to false )
DoCmd.OpenForm "frmInAuction", , , , acFormAdd, , LotId
End IF


Then in the frmInAuction On_Load event place the code
Private Sub Form_Load()
If Not IsNull(OpenArgs) Then
LotIdRef = OpenArgs
EndIf

Doing it this way means that you can use the same frmInAuction to open old records - making sure that you leave the OpenArgs parameter blank - in that case and the form will work fine but won't update the LotIdRef field.

Design the rest of the Form layout so that the user can then input all of the rest of the data that they need to. Include a simple Form_Close button so that the user drops back to frmLotInfo when the frmInAuction closes.


'ope-that-'elps.

G LS