Complex Insert into Table from Form

[klynch0803]

Good Afternoon all,

This is very complex and a little out of my ability at the time. Can someone help me out with the code or maybe create the form for me? I have attached the proper pieces of the database to work with.

Also this database is much more complex than this as it displays picture of Member, does Inventory, keeps track of Sales Income and Payouts etc and I will be happy to upload it when it is complete to this thread for other peoples use and learning tool as well in about 2 weeks when it is complete.

I'm using the following tables with appropriate fields:

MemberList
ApplicationDate
Member#
Birthdate

tdatEntryList

DLNum
EntryTime
GuestofMem
ValidAllowEntry


What I want is a form that will have a TxtField For "Scan ID" where someone would scan there Drivers Liscense for example and it would search the table MemberList Field Member# to see if it exists. If it does then it would insert into table tdatEntryList the Drivers License number scanned into table field DLNUM and the time into ENTRYTIME which is set in the table for its DEFAULT VALUE Now() and also add check mark to ValidAllowEntry.

If it doesn't Exist it would Show a (Yes/No) Dialog "Not A Valid Member! With a Member?".

If No is selected it would Insert the record and <B>not have the Checkbox selected in the tabletdatEntryList "ValidAllowEntry</B>.

If yes is selected it would then add a txtfield to the form (Previously HIdden) for "Guest Of Member?". When the Guest Of Drivers Liscense is scanned then it would then check to see if that is a valid Member and if it is then insert the "Scan ID" text field data into DLNum, current time into field ENTRYTIME and the "Guest Of" into the "GuestOfMem" in the table tdatEntryList.

If it is not a valid Member then it would say not a valid Member and insert into the table and <B>not have the Checkbox selected in the tabletdatEntryList "ValidAllowEntry</B>.



NOW in addition I need 2 other Criteria's...

One is it to take the Birthdate from table MemberList of the Valid Member and compare it to todays Short Date and if they are over 21 it would Show there age beside the Drivers Liscense number and if they are under 21 but over 18 it would show an Orange Display of there Age and if Under 18 then it would Show a REd Display of there Age and add the Record but <B>not have the Checkbox selected in the tabletdatEntryList "ValidAllowEntry</B>.

Second is it to also compare the ApplicationDate and RenewedDate in table MemberList and if one is less than 12 months from the next upcoming June 1. If it is within the 12 months it would continue to add the record If not within the 12 Months then it would Display Membership Needs to Be renewed Today and continue as normal.

 

[ajetrumpet]

Lynch,

I'm not going to decipher all of this, but I can tell you that your instructions are just written words representing a block of code. If you do not know how to put your words into Visual Basic language, I'm sure we would be more than happy to help you. But, I doubt you're going to get anyone to do all of this for you (if any of it).

And, I know you said this is out of your ability, but aside from the scanning portion of the task, I didn't read anything that was very complex in nature. It looks like you can get most of the work done here with two words: IF, THEN.

 

[klynch0803]

I understand what you are saying. I know what I want and I can do queries, ropeort and forms based on tables and queries myself. I can do very basic VB code, like close form and open form when this form closes etc but I do not know how to do insert code etc. You are correct I do not know how to change my words into VB code in this case.

This is why I'm not able to complete this form.

Hopefully someone will help me out with this project. I have tried to find something smialar that maybe I can edit some code to fit my project but have not been able to find such that I could get to work.

Thank You for your input.

 

[ajetrumpet]

Well,

People are usually a bit more receptive if you at least try to give it a shot yourself. Honestly, the post jumps out to me and says "do my work". That's not a bad thing Lynch, but it's not a good first impression, ya know? What you want might be a bit too much to do without getting compensation for it. But, who knows...

If you want to give it a shot, I will help ya. If you don't, I will still help if you want to go step by step, passing a file back and forth.

One thing I won't do though, is go through all of the work that you have presented here and then have you come back and say "That's not what I wanted". That's not meant to offend, it's simply a fact of life, and it does happen (unfortunately)!

 

[klynch0803]

Adam,

I appreciate your willingness to help and you are correct knowone learns anything from someone else doing it for them. That wasnt my true intention even though it appears that way when re-reading my original post.

I have been trying to get the form to just search the table MemberList and let me know if the record does not exists (By Pop-up), if it does insert it into the tdatentry table and mark the Allow Entry as True.

My code seems to do nothing and I'm also have my immediate window open and I dont see anything passing through it either. I keep getting debug errors.

Im only using the top text box at the moment and the Change Event.

 

[ajetrumpet]

I think you are trying to use the recordset for this. You can do it that way, but it's a bit too much work. Try the following in place of what you have now:

If DCount("memberID", "memberlist", "memberID = forms!fdatentry!scanIDprim") = 0 Then
  MsgBox "Not a Valid Member! Guest of Member? "
   
Else
  DoCmd.RunSQL "INSERT INTO tdatentrylist (dlnum) SELECT memberlist.memberID " & _ 
    "FROM memberlist WHERE memberlist.memberID = forms!fdatentry!scanIDprim"
      
End If

This code changes your field called Member# to MemberID. Gid rid of the '#' sign. That is a specific identifier in VB, and it will cause you many headaches when coding.

 

[klynch0803]

I inrted that code in place of mine in the attached database.

It tells me that no matter what I enter in the field not a valid member.

After messaging you several times I figure I take this in steps.



This is exactly what I'm looking to accomplish in step 1:

Enter the number in the form fdatEntry field "ScanIDPrim"

Code then searches table MemberList Column (MemberID(Changed on your recomendation)

If it exists it enters the ScanIDPrim number into table tdatEntryList "DLNum"
and waits for next entry to start process over.

else if does not exist it will display msgbox "Not Valid Member! Guest of Member?"

Step two would be to make the msgbox above be yes/no and do more record inserts and criterias.

 

[ajetrumpet]

The problem was in the DCOUNT function. This does work:

   If DCount("memberID", "memberlist", "memberID = " & Me.ScanIDPrim) = 0 Then
      MsgBox "Not a Valid Member! Guest of Member? "

this minor error in the function syntax is a great example of how certain quotation mark placements work in some versions of Access, but not in others.